CODEWITHSUNDEEP
sqlmysqlselectgroup-by
The87Boy
The87Boy

Reputation: 887

MySQL select minimum value from a table with a group by

Hey everyone
I have a table with an auto increment value called id, an user-id, a date (called date) and an integer for multiple registrations (called multiregistration)
As multiregistration can have more than one date I need to select the multiregistrations with the lowest date first
You can compare it by getting the minimum date for each multiregistration, where I need to get the (id for) each multiregistration. It should then sort them by the minimum date

Some sample data

 id --- user-id ---   date     --- multiregistration
 1   |    2      |  2010-02-01  |  1
 2   |    3      |  2010-02-01  |  2
 3   |    4      |  2010-01-01  |  2
 4   |    2      |  2010-02-03  |  1
 5   |    4      |  2010-02-03  |  3
 6   |    1      |  2010-02-02  |  3

Expected output

 multiregistration
  2
  1
  3

You can compare it to

 SELECT `multiregistration`
 FROM `registrations`
 ORDER BY `date`

But I want to remove duplicates

Here is the PHP code I am using right now

 // Create the query
 $query  = 'SELECT `multiregistration` AS `multireg` FROM `registrations` WHERE `multiregistration`>0 GROUP BY `multiregistration`';

 // Execute the query and set the result into a variable
 $idsTo2ndQuery = arrayExecuteLocalQueryGetResults($query);

 // Create the second Query
 $query2 = 'SELECT `multiregistration` AS `multireg` FROM `registrations` WHERE `multiregistration`='.implode(' OR `multiregistration`=', $idsTo2ndQuery).' ORDER BY `date` ASC';

 // An array of IDs (sorted)
 $ids = array();

 // Execute the query and set the result into a variable
 $idsArray = arrayExecuteLocalQueryGetResults($query2);

 // For each entry
 foreach ($idsArray AS $idArray)
      if (!in_array($idArray, $ids))
           $ids[] = $idArray;

 // Return the IDs
 return $ids;

Upvotes: 2

Views: 15726

Answers (4)

Robert Kaucher
Robert Kaucher

Reputation: 1861

Here is how I would do this in T-SQL. It declares a variable table to play with the data and the query produces the exact result requested. 

DECLARE @A AS TABLE(ID INT, USERID INT, SOMEDATE DATE, MULTIREG INT)

INSERT @A
SELECT 1,2,'20100201',1 UNION ALL
SELECT 2,3,'20100201',2 UNION ALL
SELECT 3,4,'20100101',2 UNION ALL
SELECT 4,2,'20100302',1 UNION ALL
SELECT 5,4,'20100203',3 UNION ALL
SELECT 6,1,'20100202',3

SELECT * FROM @A AS A
WHERE A.SOMEDATE = (SELECT MIN(SOMEDATE) FROM @A AS B WHERE B.MULTIREG = A.MULTIREG)
ORDER BY USERID DESC

ID  USERID  SOMEDATE     MULTIREG
3    4     2010-01-01       2
1    2     2010-02-01       1
6    1     2010-02-02       3

Upvotes: 1

John K.
John K.

Reputation: 5474

If you group by multiregistration, you'll only get one row per distinct multiregistration. This will give you the set of all the rows sorted by multiregistration first and then date.

SELECT multiregistration, date 
FROM registrations 
ORDER BY multiregistration, date

UPDATE:

This will give you a set of ids...one for each distinct multiregistration where the date is the min(date)...

SELECT id  
FROM registrations 
GROUP BY multiregistration
ORDER BY multiregistration, date

UPDATE:

This will give you a one multiregistration record...where the date is the first date in the table...

SELECT top 1 multiregistration  
FROM registrations     
ORDER BY date

UPDATE: (sample data?)

ID-----USER-ID-----DATE------------MULTIREGISTRATION
1        101            1/2/2011         3
2        101            1/1/2011         3 
3        102            1/5/2011         2
4        102            1/7/2011         2
5        103            1/1/2011         4

Minumum date is 1/1/2011 multiregistration (3) would be 1/1/2011 - id 2... This will return an ordered (by ascending date) result set where the multiregistration value is 3 

SELECT * 
FROM registrations   
WHERE ID in (SELECT min(date) FROM registrations)   
ORDER BY date

For both (actually in this case all 3 - #3, 2, and 4) in a list...the following would return a result set (shown below...)

SELECT * 
FROM registrations          
ORDER BY multiregistration, date

This returns a result set like 

3        102            1/5/2011         2
4        102            1/7/2011         2
2        101            1/1/2011         3 
1        101            1/2/2011         3
5        103            1/1/2011         4

Upvotes: 1

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

SELECT MIN(date), `multiregistration` 
FROM `registrations` 
GROUP BY `multiregistration` 
ORDER by `date`

Upvotes: 0

Ratinho
Ratinho

Reputation: 298

SELECT multiregistration FROM registrations 
GROUP BY multiregistration ORDER BY date

is it doing the work?

Upvotes: -1

Related Questions