CODEWITHSUNDEEP
sql-serversql-server-2005selectgroup-by
rjbogz
rjbogz

Reputation: 870

Select additional columns for a SELECT MIN() statement

I have this current select:

  SELECT MIN(CONVERT(DateTime,SUBSTRING(NameAndDate,14,8))),
         SUBSTRING(NameAndDate,1,12)
    FROM MyData
   WHERE pName IN (SELECT SUBSTRING(NameAndDate,1,12)
                     FROM MyData
                 GROUP BY SUBSTRING(NameAndDate,1,12)
                   HAVING COUNT(*) > 1)
GROUP BY SUBSTRING(NameAndDate,1,12)

Where SUBSTRING(NameAndDate,1,60) is the person's ID name and SUBSTRING(NameAndDate,61,8) is the date they came in.

There are many times where this data shows up multiple times in the table which is why I want to select the MIN date.

The problem is that there is another column in the table (ID) that I need to be added, but I don't want to group by it because it adds duplicates to the Person's ID.

Is there a way I can do the following:

  SELECT ID,
         MIN(CONVERT(DateTime,SUBSTRING(NameAndDate,14,8))),
         SUBSTRING(NameAndDate,1,12)
    FROM MyData
   WHERE pName IN (SELECT SUBSTRING(NameAndDate,1,12)
                     FROM MyData
                 GROUP BY SUBSTRING(NameAndDate,1,12)
                   HAVING COUNT(*) > 1)
GROUP BY SUBSTRING(NameAndDate,1,12)

EDIT:

There could be multiple times a person comes through:

 ID | NameAndDate
----+-----------------------
  1 | J60047238486 08162013
  2 | J60047238486 08182013
  3 | J60047238486 08242013
  4 | J60047238486 09032013
  5 | J60047238486 10102013
  6 | C40049872351 05302013
  7 | C40049872351 07212013
  8 | C40049872351 07252013

My current select pulls:

         Name | Date
--------------+---------------------
 J60047238486 | 08/16/2013 00:00:00
 C40049872351 | 05/30/2013 00:00:00

But I want to add the ID column for those specific rows:

 ID |     Name     | Date
----+--------------+---------------------
  1 | J60047238486 | 08/16/2013 00:00:00
  6 | C40049872351 | 05/30/2013 00:00:00

Upvotes: 0

Views: 127

Answers (3)

rs.
rs.

Reputation: 27467

Try this

SELECT * FROM (
SELECT id, 
 CONVERT(DateTime,right (SUBSTRING(NameAndDate,14,8),4) 
                            + SUBSTRING(NameAndDate,14,4)) D,

SUBSTRING(NameAndDate,1,12) N,

COUNT(*) OVER (PARTITION BY SUBSTRING(NameAndDate,1,12)) Cnt,

ROW_NUMBER() OVER (PARTITION BY SUBSTRING(NameAndDate,1,12) 
                   ORDER By SUBSTRING(NameAndDate,14,8)) rn
FROM
mydata
) v WHERE CNT > 1 and rn = 1;

SQL DEMO HERE

Upvotes: 1

roman
roman

Reputation: 117636

with cte as (
    -- first cte - parsing data
    select
        ID,
        left(NameAndDate, 12) as Name,
        convert(date,
            right(NameAndDate, 4) + 
            substring(NameAndDate, 14, 2) +
            substring(NameAndDate, 16, 2),
        112) as Date
    from Table1
), cte2 as (
    -- second cte - create row_number  
    select
        ID, Name, Date,
        row_number() over(partition by Name order by Date) as rn
    from cte
)
select
    ID, Name, Date
from cte2
where rn = 1

sql fiddle demo

Upvotes: 0

Andrew
Andrew

Reputation: 8758

You can do this, but it aint' pretty. You have to run your original query to get the minimum date for each name, and then join that back to your MyData table. It's particularly ugly because of how you store the data. Converting your MMDDYYYY string to a data was really fun.

SQL Fiddle

select
MyData.[ID],
t1.theName,
t1.theDate
from
Mydata
inner join
(
select
SUBSTRING(NameAndDate,1,12) as theName,
min (
convert(datetime,
right (SUBSTRING(NameAndDate,14,8),4) + '-' + 
left (SUBSTRING(NameAndDate,14,8),2) +  '-' + 
SUBSTRING((SUBSTRING(NameAndDate,14,8)),3,2)
))as theDate
from
mydata

where
SUBSTRING(NameAndDate,1,12) in
(SELECT SUBSTRING(NameAndDate,1,12)
                     FROM MyData
                 GROUP BY SUBSTRING(NameAndDate,1,12)
                   HAVING COUNT(*) > 1)

group by
SUBSTRING(NameAndDate,1,12) ) t1
ON SUBSTRING(mydata.NameAndDate,1,12) = t1.theName
AND (convert(datetime,
right (SUBSTRING(NameAndDate,14,8),4) + '-' + 
left (SUBSTRING(NameAndDate,14,8),2) +  '-' + 
SUBSTRING((SUBSTRING(NameAndDate,14,8)),3,2))) = t1.theDate

Upvotes: 1

Related Questions