Use of rownum function in a select

Question

Post reply

Use of rownum function in a select

@DJ

SSCommitted

Points: 1989
More actions
April 30, 2013 at 3:37 am

#277209

Hi
I need to get the first row from a subselect. Have read a couple posts on this topic, but not sure I get it.
Please find below the code.
What I need is that the route subselect only returns the first record for every route.
SELECT
(SELECT OM.ord_route, rownum r
FROM Trans T, Order OM
WHERE T."From license number." = T1."From license number."
AND T."Transaction type." = 014
AND OM.ord_key = T.ORD_KEY
AND (T."Date record was created." > T1."Date record was created." and T."Date record was created." < T1."Date record was created." + 1)
and r = 1
) as route,
T1.TO_ITEM,
T1.FROM_LOCATION,
T1.TO_LOCATION,
...............
FROM Trans as T1
WHERE
T1."From license number." like '4%'
Any ideas?
BR
Dan

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Use ROW_NUMBER() OVER(PARTITION BY OM.ord_route ORDER BY ...)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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

-- use proper join syntax

-- don't use double-quotes for identifiers unless absolutely necessary

-- write queries using TOP 1...ORDER BY and ROW_NUMBER() and choose quickest of the two

-- CROSS APPLY is often more readable than correlated subquery in SELECT list.

SELECT

x.[Route],

T1.TO_ITEM,

T1.FROM_LOCATION,

T1.TO_LOCATION,

...............

FROM Trans as T1

CROSS APPLY (

SELECT TOP 1

[Route] = OM.ord_route

FROM Trans T

INNER JOIN [Order] OM

ON OM.ord_key = T.ORD_KEY

WHERE T.[From license number.] = T1.[From license number.]

AND T.[Transaction type.] = 014

AND (T.[Date record was created.] > T1.[Date record was created.]

AND T.[Date record was created.] < T1.[Date record was created.] + 1)

ORDER BY ...

) x

WHERE T1.[From license number.] like '4%'

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

@DJ SSCommitted Points: 1989 More actions · Answer 3

ChrisM@Work: I have never used cross apply before. Will have to study that one.

I see you placed an group by...

Is this needed or optional.

I am trying your version now, withoute hte group by.

It seemes to query, bit slow maybe, I will have to evaluate when finished.

BR

Dan

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

@DJ (4/30/2013)
ChrisM@Work: I have never used cross apply before. Will have to study that one.
I see you placed an group by...
Is this needed or optional.
I am trying your version now, withoute hte group by.
It seemes to query, bit slow maybe, I will have to evaluate when finished.
BR
Dan

There's no GROUP BY in my query.

Can you show your entire query? I'd like to see if the reference to trans in the inner query is required.

The performance will depend upon available indexes - in other words if it's poor, then consider adding or changing index(es) to support seeks in the inner select. Changing indexes will be easier if there's only one table in the inner select.

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

@DJ SSCommitted Points: 1989 More actions · Answer 5

My bad 🙂 I ment Order by, not group by.

Here is the complete code

SELECT

x.[Route],

T1."To license number. (/TO_LIC)" as TO_LIC,

T1."From license number." AS FROM_LIC,

T1.TO_ITEM,

T1.FROM_LOCATION,

T1.TO_LOCATION,

T1."Transaction type." AS TransType,

T1."Populates by TRANS_NO_SEQ to generate a uniqu" as TransNo,

T1."ORD_NO" as OrderNO,

T1."Refers to ORD_KEY in ORD_MAST." as ORD_KEY,

T1."References LOAD_ID in ORD_MAST." as Load_ID_ORD_MAST,

T1."Date record was created.",

CASE

WHEN (T1.to_location like 'INN_MONO%BANE%' OR T1.to_location like 'INN_MONO_ORM%')

THEN 'INFEED'

WHEN (T1.to_location like 'INN_MONO%' OR T1.to_location like 'PRE_MONO_ORM%')

THEN 'FLOOR'

WHEN (T1.to_location like 'TROLLEY%')

THEN 'TROLLEY'

WHEN T1.to_location like 'STG%' OR T1.to_location like 'UT%GULV'

THEN 'STAGED'

WHEN (T1.to_location like 'UT_RC_AVSLAG%' OR T1.to_location like 'UT_OPPLAST%')

THEN 'OUTFEED'

ELSE 'UNKNOWN'

END as Category

FROM TRANS as T1

CROSS APPLY (

SELECT TOP 1

[Route] = OM.ord_route

FROM TRANS T

INNER JOIN ORDER OM

ON OM.ord_key = T."Refers to ORD_KEY in ORD_MAST."

WHERE T.[From license number.] = T1.[From license number.]

AND T.[Transaction type.] = 014

AND (T.[Date record was created.] > T1.[Date record was created.]

AND T.[Date record was created.] < T1.[Date record was created.] + 1)

--ORDER BY ...

) x

WHERE T1."From license number." like '4%' and

T1."From license number." not like '49%' and

T1.to_item not like 'Z%'

AND (

T1.to_location like 'INN_MONO%'

OR(T1.from_location like 'INN_MONO%' AND T1.to_location like 'INN_MONO%BANE%'

OR(( T1.from_location like 'INN_MONO%BANE%' OR T1.from_location like 'INN_MONO_ORM%' OR T1.from_location like 'LOC_MG60001' AND T1.to_location like 'TROLLEY%')

OR( T1.from_location like 'TROLLEY%' AND ( T1.to_location like 'UT_OPPLAST&' OR T1.to_location like 'UT_RC_AVSLAG%')

)

OR ((T1.from_location like 'UT_OPPLAST%' OR T1.from_location like 'UT_RC_AVSLAG%' OR T1.from_location like 'INN_MONO%')

AND(T1.to_location like 'STG%' OR T1.to_location like 'UT_OPPLAST%GULV' )

)

OR (T1.from_location like 'FULLRC_CO_EOL%' AND T1.to_location like 'INN_MONO_ORM%')

OR (T1.from_location like 'PRE_MONO_ORM%' AND T1.to_location like 'INN_MONO_ORM%')

))

)

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

You need the ORDER BY, otherwise what TOP returns is unpredictable.

You also need the reference to table trans in the subselect because the [order] table is filtered by a different set of trans rows than the main query.

What indexes do you have on both tables? Can you post the actual execution plan?

SELECT

x.[Route],

T1.[To license number. (/TO_LIC)] as TO_LIC,

T1.[From license number.] AS FROM_LIC,

T1.TO_ITEM,

T1.FROM_LOCATION,

T1.TO_LOCATION,

T1.[Transaction type.] AS TransType,

T1.[Populates by TRANS_NO_SEQ to generate a uniqu] as TransNo,

T1.[ORD_NO] as OrderNO,

T1.[Refers to ORD_KEY in ORD_MAST.] as ORD_KEY,

T1.[References LOAD_ID in ORD_MAST.] as Load_ID_ORD_MAST,

T1.[Date record was created.],

CASE

WHEN (T1.to_location like 'INN_MONO%BANE%' OR T1.to_location like 'INN_MONO_ORM%')

THEN 'INFEED'

WHEN (T1.to_location like 'INN_MONO%' OR T1.to_location like 'PRE_MONO_ORM%')

THEN 'FLOOR'

WHEN (T1.to_location like 'TROLLEY%')

THEN 'TROLLEY'

WHEN T1.to_location like 'STG%' OR T1.to_location like 'UT%GULV'

THEN 'STAGED'

WHEN (T1.to_location like 'UT_RC_AVSLAG%' OR T1.to_location like 'UT_OPPLAST%')

THEN 'OUTFEED'

ELSE 'UNKNOWN'

END as Category

FROM TRANS as T1

CROSS APPLY (

SELECT TOP 1

[Route] = OM.ord_route

FROM TRANS T

INNER JOIN [ORDER] OM

ON OM.ord_key = T.[Refers to ORD_KEY in ORD_MAST.]

WHERE T.[From license number.] = T1.[From license number.]

AND T.[Transaction type.] = 014

AND (T.[Date record was created.] > T1.[Date record was created.]

AND T.[Date record was created.] < T1.[Date record was created.] + 1)

-- ORDER BY ...

) x

WHERE T1.[From license number.] like '4%'

and T1.[From license number.] not like '49%'

and T1.to_item not like 'Z%'

AND (

T1.to_location like 'INN_MONO%'

OR (T1.from_location like 'INN_MONO%' AND T1.to_location like 'INN_MONO%BANE%'

OR (( T1.from_location like 'INN_MONO%BANE%' OR T1.from_location like 'INN_MONO_ORM%' OR T1.from_location like 'LOC_MG60001' AND T1.to_location like 'TROLLEY%')

OR ( T1.from_location like 'TROLLEY%' AND ( T1.to_location like 'UT_OPPLAST&' OR T1.to_location like 'UT_RC_AVSLAG%')

)

OR ((T1.from_location like 'UT_OPPLAST%' OR T1.from_location like 'UT_RC_AVSLAG%' OR T1.from_location like 'INN_MONO%')

AND (T1.to_location like 'STG%' OR T1.to_location like 'UT_OPPLAST%GULV' )

)

OR (T1.from_location like 'FULLRC_CO_EOL%' AND T1.to_location like 'INN_MONO_ORM%')

OR (T1.from_location like 'PRE_MONO_ORM%' AND T1.to_location like 'INN_MONO_ORM%')

))

)

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