0

basically I have a table "Songs" like this example.

enter image description here

I need to fetch the data and display in a gridview. The table is messy and songs can be in any order, not necessarily grouped by album or singers. I need to sort the rows first by singer, it should sort the artist in alphabetical order. The album should be grouped as well. But more importantly the songs should be sorted in order alphabetically/or by the number.

The table above show the ideal sorted list that I want. Can anyone point how to write T-SQL for this. I am using SQL Server 2008 R2.

Improve this question
2
  • Do you want the songs to be sorted alphabetically or by the number? You can't do both. Commented Apr 21, 2012 at 17:22
  • The songs need to be sorted just by number Thanks :) Commented Apr 22, 2012 at 18:46

3 Answers 3

Reset to default
1

You need to pick whether you want to order the songs in an album alphabetically (not likely IMHO) or by number (much more likely, since I wouldn't want to listen to Dark Side of the Moon in any order other than the original intention). As I alluded to in a comment above, you can't do both.

To sort by number:

SELECT Title, Singer, Album
  FROM dbo.Songs
  ORDER BY Singer, Album, 
    CONVERT(INT, SUBSTRING(Title, 1, CHARINDEX('.', Title)));

To sort the title alphabetically:

SELECT Title, Singer, Album
  FROM dbo.Songs
  ORDER BY Singer, Album, 
    SUBSTRING(Title, CHARINDEX('.', Title) + 1, 255); 
    -- 255 or whatever the column is defined as

A lot of people will suggest LEN(Title) but to me that's a calculation that's unnecessary. The only case where it would be justified, IMHO, is in cases where the schema is evolving rapidly and the max length of that column could not be considered "stable." In those cases I might still suggest just using a higher number that is likely to accommodate any future size increases for that column, say 4000.

As @a1ex07 hinted, you should consider storing the number and the title separately. You can do this as follows:

ALTER TABLE dbo.Songs ADD SongNumber TINYINT;
ALTER TABLE dbo.Songs ADD SongTitle VARCHAR(255);

UPDATE dbo.Songs SET 
  SongNumber = CONVERT(TINYINT, SUBSTRING(Title, 1, CHARINDEX('.', Title))),
  SongTitle  = SUBSTRING(Title, CHARINDEX('.', Title) + 1, 255);

-- once you've verified:    
ALTER TABLE dbo.Songs DROP COLUMN Title;

You'll also have to update any processes that access this table (or create a view and point them at the view). Now if you want to sort by SongNumber or SongTitle, your ORDER BY clause is much simpler. (But you still won't be able to sort by both, and it makes a lot of sense if you think about it.)

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+1 Nice answer, with the exception of micro-optimizing string manipulation. It's outlandish to even mention the cost of len in 2012.
@Andomar I don't understand, why perform computations when they're not necessary, even if the incremental cost is minimal?
And let me be clear, I'm not suggesting that code with LEN() in it needs to be re-written, as in "I see you have LEN() in there, let's double the performance of your query by removing it." That is micro-optimizing and not warranted. My objection is to LEN() being offered up as an answer, by an esteemed member of the community, when it is not necessary, wasteful, and wasn't part of the original question. While yes the "waste" is negligible it is the thinking behind it that I consider more toxic - the whole "well, it isn't that expensive..." Kind of like the "disk space is cheap" argument.
Thanks Aaron, Great answer but i guess there is no simpler solution though .. apart from breaking town the table such as Songs table, Artists table and Albums table ...
I don't think the solution is that complex if you're sorting by number. You might be able to make it a bit simpler if you put number in its own column, but that doesn't really change the query all that much. That said, if you are storing a lot of albums, it may make quite a bit of sense to break artist and album out into their own tables so that you're not repeating Pink Floyd and 'Dark Side of the Moon' for every single song on that album. Right now your schema is quite denormalized, and in some scenarios that's ok and even preferable.
1 
select  *
from    Songs
order by
        Singer
,       Album
,       Title
Improve this answer

1 Comment

This will sort numerically but doesn't consider the OP's other option (to sort the title alphabetically - presumably not including the leading 01. etc).
0

It looks that the only problem with sorting by Singer, Album,Title is that title contains number and string (which is not good since it breaks 1NF). If you are absolutely sure about title format, you can do 

ORDER BY Singer, Album,
cast(substring(Title,0,CHARINDEX( '.',Title)) as int),
substring(Title,CHARINDEX( '.',Title)+1,LEN(Title))
-- LEN(Title) should work, even though it would be more correct to put the right number of
-- characters (from position of '.' to the end)
Improve this answer

6 Comments

I don't think casting the remainder of the title as INT is going to work (maybe you meant VARCHAR(something)?). Also I think your last parenthesis is not located correctly.
@ Aaron Bertrand : Yeah, misprint while copying. Fixing
Thanks a1ex07, btw, what do you mean it breaks 1NF? I admit I am not really a front end designer and not really a db guy. can you suggest a better db interface for me pleaseee ? thanks ..
@Laurence Nyein: You are storing 2 entities in 1 column : number(for example, 01) and title(for example 'sometime'). 1NF requires all columns to be regular (keep just one attribute). I'd have one column with number(one of integer types) and varhar/varchar2 for title itself.
you reckon if shall i have one more column (may be SortOrder) storing just number (1, 2, 3) for sort order ?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.