SQL Query with multiple criteria

Posted on 2012-09-14
Last Modified: 2012-09-19
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?


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

Question by:Raahaugen
    LVL 73

    Assisted Solution

    try row_number analytic
    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)

    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

    LVL 30

    Accepted Solution


    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


    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

    LVL 30

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now