Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-02-14
3
Medium Priority
?
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
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?!

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 70

Expert Comment

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

Accepted Solution

by:
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:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

636 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