any order max, min in simple way

Question

any order max, min in simple way

aspanwar27

Grasshopper

Points: 10
More actions
July 23, 2012 at 4:16 pm

#264247

Comments posted to this topic are about the item any order max, min in simple way

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

I still consider ranking functions as the easiest one.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

aspanwar27 (7/23/2012)
Comments posted to this topic are about the item <A HREF="/scripts/92227/">any order max, min in simple way</A>

Its not clear what you're trying to do. We use these queries to get the 3rd, or 17th topmost row of a table by some order, but you're suggesting they're for min/max. It's confusing.

USE tempdb

go

DROP TABLE trnbillmain

CREATE TABLE trnbillmain (customer_id INT IDENTITY(1,1))

GO

-- insert 20 rows with customer_id 1 through 20

INSERT trnbillmain DEFAULT VALUES

GO 20

SET NOCOUNT ON

SELECT * FROM trnbillmain

-- Results

select distinct top 1 customer_id -- selects ID = 14 using 2 table scans and 2 sorts

from trnbillmain

where customer_id in (

select distinct top 7 customer_id

from trnbillmain

order by customer_id desc)

order by customer_id asc

select distinct top 1 customer_id -- selects ID = 10 using 2 table scans and 2 sorts

from trnbillmain

where customer_id in (

select distinct top 10 customer_id

from trnbillmain

order by customer_id asc)

order by customer_id desc

SELECT TOP 1 customer_id -- selects ID = 1 using 1 table scan and 1 sort

FROM trnbillmain

ORDER BY customer_id ASC

SELECT TOP 1 customer_id -- selects ID = 20 using 2 table scan and 1 sort

FROM trnbillmain

ORDER BY customer_id DESC

^{“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

Ken SSCoach Points: 17191 More actions · Answer 3

I think this was a bad example as it doesn't make any sense. Using sample data with a value field such as sales_dollars or number_of_orders would have been better. Otherwise sorting by the ID field would only require using Order By ASC or DESC as noted by Chris.

If the author could provide a more complex example where an IN statement and multiple ORDER BYs are required then we could have a better discussion. I can see needing something like this if you are trying to sort by a measure value for a top 10 and then only returning the top 1 customer of that sorted list. The newer ranking functions such as row_number(), rank() and dense_rank() would probably give a more elegant solution though.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 9:24 am

#1877853

Thanks for the script.