Solved

Query to fetch rows with matching values for just few columns column as a single row

Posted on 2014-02-14
3
278 Views
Last Modified: 2014-02-25
Hello,
I have a table with rows having same id and code value, but different Effective date.
Id         Code          EffectiveDate     cost
1          basic          01-jan-2010        5.00
1          basic          06-feb-2011        5.00
1          exclusive    09-aug-2012        15.00

Open in new window


My query needs to return only 2 rows, one for 1 basic, and another for 1 exclusive.
Result should be like:
Id            Code                 cost
1              Basic                5.00
1              Exclusive          15.00

Open in new window


DISTINCT would not work as the Effective date is different between the two records with Id=1 and Code = 'Basic'.

Thanks!
0
Comment
Question by:sath350163
3 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 39860782
I assume you want the latest date??  You don't indicate anything about which row should be selected and why ... and they both have the same price?!

SELECT
    id, code, cost
FROM (
    SELECT id, code, cost,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY EffectiveDate DESC) AS row_num
    FROM tablename
) AS derived
WHERE
    row_num = 1
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39861308
select distinct id, code, cost
from YourTable
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39861780
both code above would work, but I will just post my article for you to understand the issue and find sql code solutions like above:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

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!

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

757 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

23 Experts available now in Live!

Get 1:1 Help Now