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

Posted on 2014-02-14
Medium Priority
Last Modified: 2014-02-25
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'.

Question by:sath350163
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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?!

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

Expert Comment

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

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 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:

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

624 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