Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sql server sorting issues Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 7:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 10, 2013 2:29 AM
Points: 12, Visits: 30
Hi Guys.I need some help
I'm doing a sellers report for my boss.
I have done a top sellers and it worked fine.I have even done a worst sellers and it worked
I need to do something that is neither top nor worst.
It has to be a middle sellers report

Does sql have any syntax for sorting that out

If i sort by asc it gives me the lowest figures
If i sort by desc it gives me highest figures.

Is there any way to sort the middle figures out?
Post #1496386
Posted Thursday, September 19, 2013 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
Yes, you can sort out the middle. The easiest way to do it would be to create 2 temp tables. One for worst sellers, one for top sellers. Then join the sellers table to both of these tables and select everyone who does not exist in one of the other two tables.

Common Table Expressions would also work for this too, but I find temp tables the easiest to do for those who haven't learned CTEs yet.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1496392
Posted Thursday, September 19, 2013 7:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 10, 2013 2:29 AM
Points: 12, Visits: 30
Let me give that a try.thanks
Any easier way?
Post #1496393
Posted Thursday, September 19, 2013 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
I know half a dozen ways to do what you've requested. But any easier than the easiest way I posted above?

If you find one, let me know.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1496398
Posted Thursday, September 19, 2013 7:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 10, 2013 2:29 AM
Points: 12, Visits: 30
Thanks
Post #1496403
Posted Thursday, September 19, 2013 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
Brandie Tarvin (9/19/2013)
I know half a dozen ways to do what you've requested. But any easier than the easiest way I posted above?

If you find one, let me know.

Rather than writing the query you described as the final step of your method, wouldn't it be easier to write
SELECT * FROM #ALLSELLERS 
EXCEPT SELECT * FROM #WORSTSELLERS
EXCEPT SELECT * FROM #TOPSELLERS

or even
SELECT * FROM #ALLSELLERS EXCEPT (
SELECT * FROM #WORSTSELLERS UNION ALL SELECT * FROM #TOPSELLERS)

if you think using brackets makes it less confusing.



Tom
Post #1496570
Posted Thursday, September 19, 2013 2:52 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:39 PM
Points: 654, Visits: 211
As a side note, this does get easier in SQL 2012 with OFFSET-FETCH:

http://www.dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch

Post #1496596
Posted Thursday, September 19, 2013 3:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
Hi you could also use NTILE.

As an example
WITH sampledata AS (
SELECT *, NTILE(5) OVER (ORDER BY Numbers DESC) N
FROM (VALUES
('A',134)
,('B',122)
,('C',88)
,('D',82)
,('E',33)
,('F',22)
,('G',18)
,('H',4)
,('I',1)
,('J',1)
) SD(Book,Numbers)
)
SELECT Book, Numbers,
CASE WHEN N = 1 THEN 'TOP 20%'
WHEN N = 5 THEN 'BOTTOM 20%'
WHEN N = 3 THEN 'MIDDLE 20%'
ELSE ''
END
FROM sampledata
--WHERE N = 3

Edit: cleaned up query a bit to make it clearer
Post #1496613
Posted Friday, September 20, 2013 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
NTILE?

This is the first time I've heard of that one. Thanks, Micky.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1496812
Posted Friday, September 20, 2013 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
L' Eomot Inversé (9/19/2013)
Brandie Tarvin (9/19/2013)
I know half a dozen ways to do what you've requested. But any easier than the easiest way I posted above?

If you find one, let me know.

Rather than writing the query you described as the final step of your method, wouldn't it be easier to write
SELECT * FROM #ALLSELLERS 
EXCEPT SELECT * FROM #WORSTSELLERS
EXCEPT SELECT * FROM #TOPSELLERS

or even
SELECT * FROM #ALLSELLERS EXCEPT (
SELECT * FROM #WORSTSELLERS UNION ALL SELECT * FROM #TOPSELLERS)

if you think using brackets makes it less confusing.


Given the question, I wasn't sure if the OP knew about the EXCEPT operator, so I opted to keep it simple with the very basics I thought (s)he would know.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1496813
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse