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

What's wrong with my DISTINCT Expand / Collapse
Author
Message
Posted Tuesday, February 05, 2013 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 12,744, Visits: 31,077
looks like a copy/paste error:

ROW_NUMBER needs parenthesis:
change that query to ROW_NUMBER() and it should work fine.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416083
Posted Tuesday, February 05, 2013 2:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,507, Visits: 30,223
More like a picnic problem. That's what I get for writing code on a smartphone.

Thanks for catching the missing parens.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1416092
Posted Tuesday, February 05, 2013 2:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
:0( Now it says I'm missing the "GROUP BY" clause... (still working on it)...
Post #1416094
Posted Tuesday, February 05, 2013 2:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,507, Visits: 30,223
momba (2/5/2013)
:0( Now it says I'm missing the "GROUP BY" clause... (still working on it)...


No group by needed., forgot to take off the MAX.

WITH BaseData AS (
SELECT
rn = ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY ADMISSION_DTE DESC),
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id,
ADMISSION_DTE
FROM
CurrentTable
WHERE
[BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
)
SELECT
*
FROM
BaseData
WHERE
rn = 1;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1416102
Posted Tuesday, February 05, 2013 2:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
...I need to study this row number() thing. Will let you know how it goes when I straigten it out. Thanks for being so patient with my newbieness.
Post #1416104
Posted Tuesday, February 05, 2013 3:31 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 4,128, Visits: 5,838
momba (2/5/2013)
...I need to study this row number() thing. Will let you know how it goes when I straigten it out. Thanks for being so patient with my newbieness.


Yes, you DO need to study ROW_NUMBER(), and likely the numerous other things that come along with OVER() and what are known as "windowing functions", ESPECIALLY that you are working on SQL Server 2012, where windowing functions finally got some lovin' by the dev team!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1416133
Posted Tuesday, February 12, 2013 7:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
momba (2/5/2013)
...I need to study this row number() thing. Will let you know how it goes when I straigten it out. Thanks for being so patient with my newbieness.

It's always better to learn new things.
But in this case good old derived table will work pretty well:

select CONVERT (varchar(9),T.EMPLOYEE_ID) AS emp_id
, CONVERT(datetime,(CONVERT(varchar(8), T.ADMISSION_DTE))) AS Adm_date
, T.ADMISSION_CDE
, T.ADMIT_TYPE_CDE
, CONVERT(datetime,(CONVERT(varchar(8), T.DISC_DTE))) AS Dis_date
, T.DISC_CDE
, T.DISC_TYPE_CDE
from CurrentTable T
INNER JOIN (SELECT EMPLOYEE_ID, MAX(DISC_DTE) latest_dte
FROM CurrentTable
GROUP BY EMPLOYEE_ID ) LT ON LT.EMPLOYEE_ID = T.EMPLOYEE_ID AND LT.latest_dte = T.DISC_DTE
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
order by emp_id , adm_date

Post #1419268
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse