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

Query Help - Selecting a single ID from group

Here's my query:
SELECT ID,Status,groupcode,pt.ptID,COUNT(item.ProgramID) AS ItemCount
FROM programs p
JOIN ppType ppt ON p.ID = ppt.ProgramID
JOIN pType pt ON ppt.pptID = pt.ptID
JOIN pItem item ON p.ID = item.ProgramID
WHERE GroupCode = '019'
GROUP BY ID,[Status],groupcode,pt.ptID

My needs are pretty specific, so here it is:

First, I'm going to drop off the ID in the select. From the group of items - everything with the same item count should have a new ID column and the value should be any of the original IDs from the group - doesn't matter which one.

 
82650	Mandatory	019	2	3
82650	Mandatory	019	3	3
82650	Mandatory	019	4	3
82650	Mandatory	019	5	3
82651	Mandatory	019	2	3
82651	Mandatory	019	3	3
82651	Mandatory	019	4	3
82651	Mandatory	019	5	3
82652	Mandatory	019	9	3
82653	Mandatory	019	9	3
82654	Mandatory	019	2	2
82654	Mandatory	019	3	2
82654	Mandatory	019	4	2
82654	Mandatory	019	5	2
82654	Mandatory	019	9	2
82655	Mandatory	019	2	2
82655	Mandatory	019	3	2
82655	Mandatory	019	4	2
82655	Mandatory	019	5	2
82655	Mandatory	019	9	2

Open in new window


So here's how I'd want it to look:

Mandatory	019	2	3 82650	
Mandatory	019	3	3 82650	
Mandatory	019	4	3 82650	
Mandatory	019	5	3 82650	
Mandatory	019	2	3 82650	
Mandatory	019	3	3 82650	
Mandatory	019	4	3 82650	
Mandatory	019	5	3 82650	
Mandatory	019	9	3 82650	
Mandatory	019	9	3 82650	
Mandatory	019	2	2 82654
Mandatory	019	3	2 82654
Mandatory	019	4	2 82654
Mandatory	019	5	2 82654
Mandatory	019	9	2 82654
Mandatory	019	2	2 82654
Mandatory	019	3	2 82654
Mandatory	019	4	2 82654
Mandatory	019	5	2 82654
Mandatory	019	9	2 82654

Open in new window

0
coldchillin
Asked:
coldchillin
1 Solution
 
sshah254Commented:
Look at the first listing

82650      Mandatory      019      2      3
82650      Mandatory      019      3      3
82650      Mandatory      019      4      3
82650      Mandatory      019      5      3

What are the 3s in the last column?

Ss
0
 
coldchillinAuthor Commented:
That's an Item Count for the program
0
 
ThomasianCommented:
SELECT Status,groupcode,pt.ptID,COUNT(item.ProgramID) AS ItemCount
     ,MIN(ID) OVER (PARTITION BY COUNT(item.ProgramID)) NewID
FROM programs p
JOIN ppType ppt ON p.ID = ppt.ProgramID
JOIN pType pt ON ppt.pptID = pt.ptID
JOIN pItem item ON p.ID = item.ProgramID
WHERE GroupCode = '019'
GROUP BY ID,[Status],groupcode,pt.ptID
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
SharathData EngineerCommented:
try this.
;with CTE as (
SELECT ID,Status,groupcode,pt.ptID,COUNT(item.ProgramID) AS ItemCount
FROM programs p
JOIN ppType ppt ON p.ID = ppt.ProgramID
JOIN pType pt ON ppt.pptID = pt.ptID
JOIN pItem item ON p.ID = item.ProgramID
WHERE GroupCode = '019'
GROUP BY ID,[Status],groupcode,pt.ptID)
select Status,groupcode,ptID,ItemCount,MIN(ID) over (partition by Status,groupcode,ptID,ItemCount) ID
  from CTE

Open in new window

0
 
SharathData EngineerCommented:
correction in previous post.
;with CTE as (
SELECT ID,Status,groupcode,pt.ptID,COUNT(item.ProgramID) AS ItemCount
FROM programs p
JOIN ppType ppt ON p.ID = ppt.ProgramID
JOIN pType pt ON ppt.pptID = pt.ptID
JOIN pItem item ON p.ID = item.ProgramID
WHERE GroupCode = '019'
GROUP BY ID,[Status],groupcode,pt.ptID)
select Status,groupcode,ptID,ItemCount,MIN(ID) over (partition by ItemCount) ID
  from CTE

Open in new window

0
 
coldchillinAuthor Commented:
Thanks for the help, and Sharath, that also worked basically, but I cannot use a Common Table Expression in my application layer.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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