Sort Data ??? different one !!!

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  October 21, 2011 at 4:28 am

  #390670

  Hi All,

  I have a table with below data

  create table t1

  (

  name varchar(50)

  )

  insert into t1

  select 'anbu'

  union

  select 'balu'

  union

  select 'Azhagu'

  union

  select 'Chitra'

  union

  select 'Babu'

  union

  select 'chennai'

  union

  select 'Bama'

  expected output:

  anbu

  Azhagu

  balu

  Babu

  Bama

  chennai

  Chitra

  Basically the lower case letter should come first for each and every letter.

  Inputs are welcome!

  karthik

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  October 21, 2011 at 4:54 am

  #1397852

  does this work for you:

  order by LEFT(name,1), ASCII(name) desc

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 21, 2011 at 5:14 am

  #1397861

  SELECT *

  FROM (

  select Name = 'anbu' union ALL

  select 'balu' union ALL

  select 'Azhagu' union ALL

  select 'Chitra' union ALL

  select 'Babu' union ALL

  select 'chennai' union ALL

  select 'Bama') d

  ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC, Name

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  October 21, 2011 at 5:49 am

  #1397881

  order by LEFT(name,1), ASCII(name) desc

  How it works internally?

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  October 21, 2011 at 5:50 am

  #1397883

  ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC, Name

  I still don't understand the working mechanism of these queries.

  karthik

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 21, 2011 at 5:56 am

  #1397889

  karthikeyan-444867 (10/21/2011)

  ---

  order by LEFT(name,1), ASCII(name) desc

  How it works internally?

  Of the 2 solutions, I think this is the one that works for you (watch the extra columns to see what's going on) :

  SELECT Name, LEFT(Name,1), ASCII(Name)

  FROM (

  select Name = 'anbu' union ALL

  select 'balu' union ALL

  select 'Azhagu' union ALL

  select 'Chitra' union ALL

  select 'Babu' union ALL

  select 'chennai' union ALL

  select 'Bama') d

  --ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC, Name

  ORDER BY LEFT(Name,1), ASCII(Name) desc

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 21, 2011 at 5:57 am

  #1397890

  karthikeyan-444867 (10/21/2011)

  ---

  ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC, Name

  I still don't understand the working mechanism of these queries.

  COLLATE changes the collation, or the way sql handles text.

  CS means case sensitive

  AS means accent sensitive.

  For all functions used you can get all the info you need in books online.

  Let us know if something else is not clear.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 21, 2011 at 5:59 am

  #1397891

  karthikeyan-444867 (10/21/2011)

  ---

  ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC, Name

  I still don't understand the working mechanism of these queries.

  Have a gander at BOL COLLATE section. When you specify ORDER BY in a query, what "template" order is SQL Server using to sort your results? The collation I specified is case sensitive (_CS_), so bob, brenda and brian will appear before (or after) Bob, Brenda and Brian.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  October 21, 2011 at 6:08 am

  #1397899

  yes...

  ORDER BY LEFT(Name,1), ASCII(Name) desc

  --> works fine.

  anbua97

  AzhaguA65

  balub98

  BabuB66

  BamaB66

  chennaic99

  ChitraC67

  the second one is not giving the expected result set.

  chennaic99

  ChitraC67

  balub98

  BabuB66

  BamaB66

  anbua97

  AzhaguA65

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  October 21, 2011 at 6:09 am

  #1397901

  how to know what is the default collating sequence assigned for any database?

  karthik

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 21, 2011 at 6:13 am

  #1397904

  karthikeyan-444867 (10/21/2011)

  ---

  how to know what is the default collating sequence assigned for any database?

  SELECT

  name

  , database_id

  , collation_name

  , *

  FROM

  sys.databases

  ORDER BY

  1