• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

selecting specific rows from returned data set in sql server 2005

Hi,
      I have the following query;
select
Number,
Reported_Date,
Priority
FROM #temp
order by Incident_Number asc, Reported_Date asc

which returns the output illustrated in Current Output.

I want to ammend this query to return the output illustrated in Desired Output.
That is one row for each number based on the  earliest value of Reported_Date.
Any help appreciated , thanks.
 singleRows
0
blossompark
Asked:
blossompark
  • 3
  • 2
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
Try this query
select * from
(
select Number, max(Reported_Date) as Reported_Date from #temp
group by Number
) a
inner join #temp t on a.Number = t.Number and a.Reported_Date = t.Reported_Date

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Minor correction
select t.* from
(
select Number, max(Reported_Date) as Reported_Date from #temp
group by Number
) a
inner join #temp t on a.Number = t.Number and a.Reported_Date = t.Reported_Date

Open in new window

0
 
blossomparkAuthor Commented:
Hi RajkumarGS:
thanks for your response,,will try now and update you
One question,
should that be min(Reported_Date) ?
I want the earliest Reported Date rows returned?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
SELECT Number, Reported_Date, Priority
FROM (
    SELECT Number, Reported_Date, Priority,
        ROW_NUMBER() OVER (PARTITION BY Number ORDER BY Reported_Date) AS row_num
    FROM #temp
) AS derived
WHERE row_num = 1
ORDER BY Number
0
 
Rajkumar GsSoftware EngineerCommented:
>> should that be min(Reported_Date) ? <<

yes. if you want to get records based on earlies, modify with min.
0
 
blossomparkAuthor Commented:
Hi RajkumarGS and ScottPletcher,
Both solutions work!!
thanks again....I can go home now thanks to you!!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now