Solved

# SQL Query with multiple criteria

Posted on 2012-09-14
487 Views
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
KDQ           01.02.2012   03.02.2012 11:24             4

Output to Excel through the Query:
Initials      /      Date      /      CreatedOn      /      Amount
KDQ           01.02.2012   03.02.2012 11:24             4

Thanks beforehand!

Best regards

/Raahaugen
0
Question by:Raahaugen

LVL 73

Assisted Solution

try row_number analytic
ee.txt
0

LVL 25

Assisted Solution

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)
``````

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
``````
0

LVL 30

Accepted Solution

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
``````
0

Author Closing Comment

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

LVL 30

Expert Comment

Welcome!
0

## Featured Post

### Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…