Selecting data with a prioity and a group by

[thegeezza]

Dear All,

I have a data set that I wish to select unique records from based on the group which they belong to and a priority.

The code below displays three records within a group (indicated by gr_id1). (Note:key1 is unique, ID is not)

select s_id,key1, company, gr_id1, priority 
from LDV_selection_pool
where gr_id1=5000034

ID              key1    company         gr_id1  priroity 
01669417	0759830	First Plc	5000034	1
701947    	0372485	First	        5000034	3
702148    	0372486	First	        5000034	3

I wish to select one record from each group, the one that has the lowest priority.

The code below is not correct but highlights what I wish to do.

select * 
from LDV_selection_pool
where key1 in(
		select min(priority), key1
		from LDV_selection_pool
		group by gr_id1
		)

Any assistance would be very much appreciated.

Regards,
Geez

 

[r937]

select * 
  from LDV_selection_pool as X
 where priority
     = ( select min(priority)
           from LDV_selection_pool
          where gr_id1 = X.gr_id1 )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[thegeezza]

Thanks r937 for the reply.

The query you have posted will select the lowest priority record/s but does not select one record per group(gr_id1).

Below is an example of a group returned by thge query you outlined. I only wish to select one of these records.

ID      key1    company                   gr_id1  priroity 
2689821 0481432	007 Decoratoring Services 5000002 3
2690951 0481433	007 Decoratoring Services 5000002 3

 

[r937]

you said "the one that has the lowest priority"

perhaps you meant "the one that has the lowest ID of all the ones that have the same lowest priority"

or perhaps the highest ID instead of the lowest?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[thegeezza]

Sorry perhaps I was not clear. I said:

"I wish to select ONE record from each GROUP, (that one record being) the one that has the lowest priority."

The min or max, whichever does not matter really, the point is having the ability to select one or the other, for one record from each group.

The subquery shown in the original post produces a set containg the correct records but does not contain all the columns for a row....

select min(priority)
        from LDV_selection_pool
        group by gr_id1

i.e. it does not provide key1 (a value that uniquely undentifies each row) that could be used to select the entire row. Which was the overall aim of my original sql query.

I hope this clears things some what.

cheers,
Geez

 

[rac2]

A lot depends on whether the priority rating is unique. It might be unique over all items or unique within a gr_id1 group. If the priorities are rank orders, so to speak, with every item having a different rank or priority then that can be used to JOIN the GROUP BY query to the basic table and thus obtain the key1.

If the priority is not unique, as your example shows, then you have the problem of choosing one of the several that have top priority. (in itself, a non-sequitor). For example, we have three critical items, two important items, and fourteen would-be-nice items.

Assuming, we have unique ranks over all items, not just within a group. Slightly different example data.

ID                 key1          company     gr_id1  priroity 
01669417    0759830    First Plc      5000034    11
701947        0372485    First            5000034    13
702148        0372486    First            5000034    37

Then

SELECT  sp.key1,
             sp.gr_id1,
             sp.priority AS "OverallPriority"
             g.gr_id1 AS "Group",
             g.HighestPriority,
             sp.* 

FROM LDV_selection_pool sp
JOIN  (
          SELECT gr_id1, MIN(priority) AS "HighestPriority"
          FROM LDV_selection_pool
          GROUP BY gr_id1
         ) g
              ON g.gr_id1 = sp.gr_id1
                    AND g.HighestPriority = sp.priority

The GROUP BY subquery gives the minimum priority ranking within each group and the gr_id1. This is JOINed to the basic table. For each set of rows in the basic table from a particular gr_id1, retrieve the one with a priority equal to the minimum for the group. Show the key for that row as well as the gr_id1 and the value of the priority. Just for fun also show the corresponding values from the subquery. And show any other interesting details about the group priority from the base table.

The subquery acts like a filter. Only the rows with minimum priority in their group will be retrieved from the base table.

I believe this will also work if the priorities are unique within a group. And if not then you will retrieve all of the high priority items for each group.

Geezers Rule!

 

[r937]

rac2, your query still returns 2 rows for 5000002

(i tested both mine and yours)

geez, try this --

select * 
  from LDV_selection_pool as X
 where key1
     = ( select max(key1)
           from LDV_selection_pool 
          where gr_id1 = X.gr_id1 
            and priority
              = ( select min(priority)
                    from LDV_selection_pool
                   where gr_id1 = X.gr_id1 ))

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[thegeezza]

Excellent work r937, that's the ticket!

Thank you all for your replies!!!

cheers,
Geez

 

[rac2]

Hello Rudy,

Yes, my query returns two rows because there are multiple items with the same priority in that group.

You have to wonder about the meaning of a requirement such as "Show me any one of the high priorities in the group, I dont care which one". So let it be the MAX(key1). From reading your posts over the months, I know you understand my point.

At any rate, thanks for taking a close look at my suggestion.

Regards.