SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rows with Min() values


Rows with Min() values

Author
Message
popq79 34287
popq79 34287
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 57
Hi All
Can someone help me how to select the row with min(E_DATE+E_Time) for each PATIID from the below table?

 

PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00001 AAA 01/01/2013 01/01/2013 14:00:00 A2505
00002 BBB 01/03/2012 03/04/2012 15:45:00 G2582
00002 BBB 01/01/2012 01/02/2012 13:45:00 P4484
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402
...
...



Results need to be like below (ie. min (E_DATE+E_TIME) for each PATID

 
PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402



Please note I'm using SQL2000.

Thanks in advance
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26852 Visits: 17557
I see you are pretty new around here. Can you please take a look at the first link in my signature for best practices when posting questions?

Also, if at all possible you should ALWAYS store date time information in a datetime field. From what you posted I am guessing these are stored as varchar values. As we work through your issue you will see how painful that can be.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
popq79 34287
popq79 34287
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 57
Sorry my mistake, these are datetime fields (but I'm using 2 fields - one to store date and the other for time)

Is there a way to do a select to obtain the result I want?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26852 Visits: 17557
popq79 34287 (1/23/2013)
Sorry my mistake, these are datetime fields (but I'm using 2 fields - one to store date and the other for time)


What are trying to gain there? The datetime datatype holds both.


Is there a way to do a select to obtain the result I want?


Yes as I said please see the link in my signature for best practices. If you can post ddl (create table scripts), sample data (insert statements) you will find lots of people willing and able to help. Given the sample you posted it looks like maybe E_ID is your primary key?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Richard Warr
Richard Warr
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2812 Visits: 1987
... sorry, didn't notice you were on SQl Server 2000. I han an answer for 2005+.

_____________________________________________________________________
MCSA SQL Server 2012
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26852 Visits: 17557
popq79 34287 (1/23/2013)
Hi All
Can someone help me how to select the row with min(E_DATE+E_Time) for each PATIID from the below table?

 

PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00001 AAA 01/01/2013 01/01/2013 14:00:00 A2505
00002 BBB 01/03/2012 03/04/2012 15:45:00 G2582
00002 BBB 01/01/2012 01/02/2012 13:45:00 P4484
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402
...
...



Results need to be like below (ie. min (E_DATE+E_TIME) for each PATID

 
PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402



Please note I'm using SQL2000.

Thanks in advance


I cobbled together an example of this but I don't quite understand your business rules here. You said you wanted to get the earliest for each PATID but for PATID 00002 you did not get the earliest. The earliest one would be for E_ID P4484 not M1402 as you posted.

Here is the example of what I think you want.


IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL
drop table #SomeTable

create table #SomeTable
(
PATID char(5),
Name char(3),
A_DATE datetime,
E_DATE datetime,
E_TIME datetime,
E_ID char(5) primary key
)

insert #SomeTable
select '00001', 'AAA', '01/01/2013', '01/01/2013', '06:00:00', 'X9625' union all
select '00001', 'AAA', '01/01/2013', '01/01/2013', '14:00:00', 'A2505' union all
select '00002', 'BBB', '01/03/2012', '03/04/2012', '15:45:00', 'G2582' union all
select '00002', 'BBB', '01/01/2012', '01/02/2012', '13:45:00', 'P4484' union all
select '00002', 'BBB', '01/01/2012', '04/01/2012', '11:45:00', 'M1402'


select st.*
from #SomeTable st
join
(
select PATID, MIN(E_DATE + E_TIME) as E_DATETIME
from #SomeTable
group by PATID
) x on x.PATID = st.PATID and x.E_DATETIME = st.E_DATE + st.E_TIME



Please notice how I posted a sample table and populated with data. This is what we would like to see when posting questions. This way we all know what the datatypes are and the people who help answer questions (all volunteer) can get to work on the issue instead of setting up the problem.

I still don't quite get why you want to have 2 datetime fields to hold one piece of information. Especially when you have to constantly add them together to get the whole thing. w00t

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search