[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

SQL Query with multiple criteria

Hello Experts!

I have a SQL query used to get data from a SQL Server 2008 to an Excel sheet.

My problem is I have four (in this case relevant) fields;
- Initials (string)
- Date (date - no time)
- CreatedOn (date and time)
- Amount (number)

There are some rows, where the Initials and Date fields are equal, and in these cases, I would like the query to output only the row containing the latest CreatedOn timestamp. Please let me know how this is possible in the "WHERE" statement?

Example:

From the Database:
Initials      /      Date      /      CreatedOn      /      Amount
ADI            01.02.2012   03.02.2012 08:34             6
ADI            01.02.2012   03.02.2012 10:21            12
ADI            01.03.2012   03.02.2012 10:23            15
KDQ           01.02.2012   03.02.2012 11:24             4

Output to Excel through the Query:
Initials      /      Date      /      CreatedOn      /      Amount
ADI            01.02.2012   03.02.2012 10:21            12
ADI            01.03.2012   03.02.2012 10:23            15
KDQ           01.02.2012   03.02.2012 11:24             4

Thanks beforehand!

Best regards

/Raahaugen
0
Raahaugen
Asked:
Raahaugen
3 Solutions
 
sdstuberCommented:
try row_number analytic
ee.txt
0
 
lwadwellCommented:
Yes and no ...

Yes for Method 1:
SELECT Initials, Date, CreatedOn, Amount
FROM your_table a
WHERE CreatedOn in (SELECT MAX(CreatedOn) FROM your_table b WHERE a.Initials = b.Initials AND a.Date = b.Date)

Open in new window


Not quite for Method 2 (this is better however):
SELECT Initials, Date, CreatedOn, Amount
FROM (SELECT Initials, Date, CreatedOn, Amount, row_number()over(partition by Initials, Date order by CreatedOn DESC) rn
      FROM your_table a) v
WHERE rn = 1

Open in new window

0
 
hnasrCommented:
Try:

SELECT tbl.Initials,tbl.Date,tbl.CreatedOn, tbl.Amount
FROM tbl
INNER JOIN (select Initials, Date, max(CreatedOn) AS maxCreatedOn from tbl group by Initials, Date)as x
ON x.Initials=tbl.Initials AND x.Date=tbl.Date AND x.maxCreatedOn=tbl.CreatedOn

Open in new window

0
 
RaahaugenAuthor Commented:
Every one of your solutions seems to work just fine.

Thanks - it helped me figure the logic in these situations.

Have a good day

Best regards

/Raahaugen
0
 
hnasrCommented:
Welcome!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now