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

Use of rownum function in a select Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 3:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:29 AM
Points: 97, Visits: 189
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, [color=#F90A0A]rownum r[/color]
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)
[color=#F90A0A]and r = 1[/color]
) 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
Post #1447932
Posted Tuesday, April 30, 2013 3:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,678, Visits: 19,552
Use
ROW_NUMBER() OVER(PARTITION BY OM.ord_route ORDER BY ...)



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1447933
Posted Tuesday, April 30, 2013 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
-- 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1447940
Posted Tuesday, April 30, 2013 5:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:29 AM
Points: 97, Visits: 189
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
Post #1447957
Posted Tuesday, April 30, 2013 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
@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
Exploring Recursive CTEs by Example Dwain Camps
Post #1447978
Posted Tuesday, April 30, 2013 6:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:29 AM
Points: 97, Visits: 189
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%')
))
)
Post #1447985
Posted Tuesday, April 30, 2013 7:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1448010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse