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 11:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
I've got another noob question...

When I run this query to just grab the emp_id I get 6474 but when I request more fields I get 6605?
select distinct CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id
, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date
, ADMISSION_CDE
, ADMIT_TYPE_CDE
, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date
, DISC_CDE
, DISC_TYPE_CDE
from CurrentTable
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
order by emp_id , adm_date

...I just want the record for the most recent adm_date what's up with my distinct?
Post #1416018
Posted Tuesday, February 05, 2013 11:38 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 22,504, Visits: 30,209
momba (2/5/2013)
I've got another noob question...

When I run this query to just grab the emp_id I get 6474 but when I request more fields I get 6605?
select distinct CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id
, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date
, ADMISSION_CDE
, ADMIT_TYPE_CDE
, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date
, DISC_CDE
, DISC_TYPE_CDE
from CurrentTable
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
order by emp_id , adm_date

...I just want the record for the most recent adm_date what's up with my distinct?


The problem is with your understanding of the DISTINCT operator. It returns all distinct records based on all the columns in the select list. Each row will be distinct.



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 #1416020
Posted Tuesday, February 05, 2013 11:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Distinct just removes complete duplicate rows (rows where all the columns you select are exactly the same)

What is that query supposed to return?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1416021
Posted Tuesday, February 05, 2013 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
I'm trying to pull out the last record for each employee, i.e. if they appear in the table two or more times, I just want the record attached to the later adm_date (which should result in just 6474 records).

Post #1416023
Posted Tuesday, February 05, 2013 12:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
momba (2/5/2013)
I'm trying to pull out the last record for each employee, i.e. if they appear in the table two or more times, I just want the record attached to the later adm_date (which should result in just 6474 records).



if you want the latest date, you want to use GROUP BY., and actually use the MAX*() function on the date so you get that last(latest/max date
SELECT
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS emp_id,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date,
ADMISSION_CDE,
ADMIT_TYPE_CDE,
CONVERT(DATETIME, (CONVERT(VARCHAR(8), DISC_DTE))) AS Dis_date,
DISC_CDE,
DISC_TYPE_CDE
FROM CurrentTable
WHERE [BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
GROUP BY
CONVERT (VARCHAR(9), EMPLOYEE_ID),
ADMISSION_CDE,
ADMIT_TYPE_CDE,
CONVERT(DATETIME, (CONVERT(VARCHAR(8), DISC_DTE))),
DISC_CDE,
DISC_TYPE_CDE
ORDER BY
emp_id,
adm_date



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 #1416024
Posted Tuesday, February 05, 2013 12:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:43 PM
Points: 4,128, Visits: 5,836
This could be more efficient (esp if "pkstuff" tight and/or the table is indexed properly for the correlated SELECT):

select CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id
, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date
, ADMISSION_CDE
, ADMIT_TYPE_CDE
, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date
, DISC_CDE
, DISC_TYPE_CDE
from CurrentTable ct1
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
and ct1.admission_dte = (SELECT MAX(ct2.admission_dte) from curenttable ct1 WHERE ct1.pkstuff = ct2.pkstuff)
order by emp_id , adm_date

You can also solve this with this construct: , ROW_NUMBER() OVER(partition by ... order by admission_dte desc) as rownumber

WHERE rownumber = 1



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1416030
Posted Tuesday, February 05, 2013 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
RE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...

SELECT
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
from CurrentTable
WHERE [BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
GROUP BY
EMPLOYEE_ID

.... but as soon as I add in additional fields the numbers get higher and higher. ???

I'm going to try the next example.
Post #1416039
Posted Tuesday, February 05, 2013 12:49 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 22,504, Visits: 30,209
momba (2/5/2013)
RE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...

SELECT
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
from CurrentTable
WHERE [BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
GROUP BY
EMPLOYEE_ID

.... but as soon as I add in additional fields the numbers get higher and higher. ???

I'm going to try the next example.


This:


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,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
FROM
CurrentTable
WHERE
[BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
)
SELECT
*
FROM
BaseData
WHERE
rn = 1;


Any columns you need added, ad to the SELECT in the CTE. You can also define which columns to select from the CTE instead of using the *, this way you can eliminate the column rn.



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 #1416046
Posted Tuesday, February 05, 2013 1:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
Lynn Pettis (2/5/2013)
momba (2/5/2013)
RE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...

SELECT
CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
from CurrentTable
WHERE [BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
GROUP BY
EMPLOYEE_ID

.... but as soon as I add in additional fields the numbers get higher and higher. ???

I'm going to try the next example.


This:


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,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
FROM
CurrentTable
WHERE
[BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
)
SELECT
*
FROM
BaseData
WHERE
rn = 1;


Any columns you need added, ad to the SELECT in the CTE. You can also define which columns to select from the CTE instead of using the *, this way you can eliminate the column rn.


... for some reason SSMS 2012 doesn't like "OVER" in the "ROW NUMBER OVER" syntax. I'm working through it now...
Post #1416076
Posted Tuesday, February 05, 2013 1:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
Cause Lynn left out some brackets

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,
MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date
FROM
CurrentTable
WHERE
[BranchID] = '950'
AND ( DISC_DTE IS NULL
OR ( DISC_DTE > 20120630
AND DISC_DTE < 20130201 ) )
)
SELECT
*
FROM
BaseData
WHERE
rn = 1;




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1416081
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse