Sort by zero vs. non-zero then by date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sort by zero vs. non-zero then by date

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2009-04-22 : 03:12:46

Let's say I have a table that looks like the following:

CREATE TABLE [MyTable](
	[ID] [int] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Available] [numeric](18, 0) NOT NULL
)
INSERT INTO MyTable VALUES(1, '2009-04-16', 0)
INSERT INTO MyTable VALUES(2, '2009-04-17', 1)
INSERT INTO MyTable VALUES(3, '2009-04-18', 3)
INSERT INTO MyTable VALUES(4, '2009-04-19', 0)

What I want to do is select from this table in such a way that records where Available > 0 are shown first sorted by Timestamp followed by records where Available = 0. For an example of what I don't want, here's what I've actually been using:

SELECT * FROM MyTable ORDER BY Available DESC, Timestamp

This, of course, returns the following:

3, '2009-04-18', 3
2, '2009-04-17', 1
1, '2009-04-16', 0
4, '2009-04-19', 0

Notice that the first 2 records are the inverse of what I actually want. Is there a way to sort this table to do what I want?

(I hope I was clear enough. I'm not sure of the best way to describe exactly what I'm looking for. Feel free to ask for clarification if needed.)

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2009-04-22 : 03:21:26

[code]
order by
case when available > 0 then 0 else 1 end,
[Timestamp]
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 03:41:03

This will work

DECLARE	@Sample TABLE
	(
		ID INT,
		ts DATETIME,
		Available TINYINT
	)

INSERT	@Sample
SELECT	1, '2009-04-16', 0 UNION ALL
SELECT	2, '2009-04-17', 1 UNION ALL
SELECT	3, '2009-04-18', 3 UNION ALL
SELECT	4, '2009-04-19', 0

SELECT		*
FROM		@Sample
ORDER BY	SIGN(Available) DESC,
		ts

E 12°55'05.63"
N 56°04'39.26"

VentureFree
Starting Member

19 Posts

Posted - 2009-04-22 : 03:42:02

Aha! I almost feel stupid for not seeing the answer for myself. It just didn't occur to me that one could use a CASE statement in the ORDER BY clause. Thank you very much, LarsG.

VentureFree
Starting Member

19 Posts

Posted - 2009-04-22 : 03:43:34

Peso: That's the kind of answer I was fearing, but thought might end up being necessary. Thanks for the input, though.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 03:46:48

I changed my answer. Have a look.

E 12°55'05.63"
N 56°04'39.26"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-22 : 09:31:06

SELECT * FROM MYTABLE
ORDER BY CASE WHEN AVAILABLE > 0 THEN 1 ELSE 2 END ,CASE WHEN AVAILABLE > 0 THEN TIMESTAMP END ASC

Iam a slow walker but i never walk back

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 10:08:26

Will not work. It returns

ID	ts	Available
2	2009-04-17 00:00:00.000	1
3	2009-04-18 00:00:00.000	3
4	2009-04-19 00:00:00.000	0
1	2009-04-16 00:00:00.000	0

with the sample code posted 04/22/2009 : 03:41:03

The problem is the second CASE. Drop the second CASE and use timestamp column only.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources