Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: open

Message Too Old, No Replies

Not sure how to query this?

     
12:51 pm on Jul 14, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts: 186
votes: 0


Hello all,
I have a query that is getting info and sorting alphabetically on a text column...no problem.

Now the client wants to sort alphabetically but at the top of the list he wants all rows that contain the string "CD" before the rest of them.

Is there an easy query to do this?

Thanks!
Chris
1:29 pm on July 14, 2010 (gmt 0)

Full Member

10+ Year Member

joined:Sept 24, 2002
posts:214
votes: 0


Using Sql Server you do this
SELECT 'CD'+<ColumnName> FROM <TableName>

But you need to make sure that the Column is a string datatype.
1:43 pm on July 14, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts:186
votes: 0


I need to get all columns of data, will this work for that? I need all columns but sorted starting where ColA contains "CD".
1:55 pm on July 14, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts:186
votes: 0


OK...solved my problem but had to use 2 querys to do it.

First get all that contain "CD", second get all that don't contain "CD":

...WHERE product LIKE '%CD%'...


...WHERE product NOT LIKE '%CD%'...
2:27 pm on July 14, 2010 (gmt 0)

Full Member

10+ Year Member

joined:Sept 24, 2002
posts:214
votes: 0


Sorry Completely miss read your original post my suggesting was no help at all
7:14 pm on July 14, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


You are not going to need two queries. Related to this thread [webmasterworld.com], try an expression.

select * from table order by field like '%CD%' desc, field asc;

You many need to change the like to 'CD%' or even field = 'CD' if it's the total content of the field, or use a regex, but that will do it in one select.
7:28 pm on July 14, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts:186
votes: 0


thanks, will give that a go!