Login or Register to Ask a Question and Join Our Community


Programming

Select several minimum values from row (MySQL)

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Select several minimum values from row (MySQL)
# 1  
Old 06-02-2010
Question Select several minimum values from row (MySQL)
Hello there.
I've got the query like that

Code:
SELECT count(tour_id) AS cnt
FROM orders 
JOIN tours 
ON orders.tour_id=tours.id
GROUP BY tour_id

The result Is
Code:
cnt 
1
4
2
1
1

Now i have to select all records with minimum values in field "cnt"
MySQL function min() returns only one.
So the result will look like that
Code:
cnt 
1
1
1

WBR
# 2  
Old 06-02-2010
Quote:
Originally Posted by Trump
...
Code:
SELECT count(tour_id) AS cnt
FROM orders 
JOIN tours 
ON orders.tour_id=tours.id
GROUP BY tour_id

The result Is
Code:
cnt 
1
4
2
1
1

Now i have to select all records with minimum values in field "cnt"
MySQL function min() returns only one.
So the result will look like that
Code:
cnt 
1
1
1

...
Code:
mysql>
mysql>
mysql> --
mysql> SELECT count(tour_id) AS cnt
    -> FROM orders
    -> JOIN tours
    -> ON orders.tour_id=tours.id
    -> GROUP BY tour_id;
+-----+
| cnt |
+-----+
|   1 |
|   4 |
|   2 |
|   1 |
|   1 |
+-----+
5 rows in set (0.00 sec)
mysql>
mysql>
mysql> --
mysql> select cnt
    -> from (select count(o.tour_id) as cnt
    ->       from orders o, tours t
    ->       where o.tour_id = t.id
    ->       group by o.tour_id) p
    ->       where cnt = (select min(cnt)
    ->                    from (select count(o.tour_id) as cnt
    ->                          from orders o, tours t
    ->                          where o.tour_id = t.id
    ->                          group by o.tour_id
    ->                         ) p
    ->                   );
+-----+
| cnt |
+-----+
|   1 |
|   1 |
|   1 |
+-----+
3 rows in set (0.00 sec)
mysql>
mysql>

tyler_durden
This User Gave Thanks to durden_tyler For This Post:
Trump
# 3  
Old 06-02-2010
durden_tyler, I've impressed! Thank you very much!
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Emergency UNIX and Linux Support

[Solved] Mysql - Take data from row and copy it to another row

Sorry if I repost my question in this section, but I'm really in a hurry since I have to finish my work... :( Dear community, I have a table with two rows like: Row1 Row2 ======= ======= 7,3 text 1 1,3 text 2 1,2,3 blabla What i need to do is add/copy... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

2. Programming

Computations using minimum values

I have the following code and into into trying to simplifying it. Any suggestions please? pmin = min (p(1), p(2), p(3), p(4), p(5), p(6)) ni = 0 xint = 0.0 yint = 0.0 zint = 0.0 !--------------------------------------------- ! if ((0.99999 * p(1)) <= pmin) then ... (3 Replies)
Discussion started by: kristinu
3 Replies

3. Shell Programming and Scripting

How to find a minimum value of a row?

input 1 2 3 4 5 2 8 2 1 1 1 4 2 1 5 4 4 4 2 1 3 2 2 6 7 4 5 4 5 5 5 4 3 3 5 I woud like to print a min of each row such that my output would look like 1 1 1 2 3 (5 Replies)
Discussion started by: johnkim0806
5 Replies

4. Shell Programming and Scripting

In a row, replace negative sign and find minimum value among four columns

Hi Friends, I have an input file like this chr1 100 200 1 2 3 4 chr1 150 200 4 5 6 7 chr2 300 400 9 6 7 1 chr2 300 410 -10 21 -11 13 chr3 700 900 -21 -22 130 165 Now, my output file is chr1 100 200 1 chr1 150 200 4 chr2 300 400 1 chr2 300 410 10 chr3 700 900 21 Remove... (5 Replies)
Discussion started by: jacobs.smith
5 Replies

5. Shell Programming and Scripting

Print minimum and maximum values using awk

Can I print the minimum and maximum values of values in first 4 columns ? input 3038669 3038743 3037800 3038400 m101c 3218627 3218709 3217600 3219800 m290 ............. output 3037800 3038743 m101c 3217600 3219800 m290 (2 Replies)
Discussion started by: quincyjones
2 Replies

6. UNIX for Dummies Questions & Answers

Select 2 columns and transpose row by row

Hi, I have a tab-delimited file as follows: 1 1 2 2 3 3 4 4 a a b b c c d d 5 5 6 6 7 7 8 8 e e f f g g h h 9 9 10 10 11 11 12 12 i i j j k k l l 13 13 14 14 15 15 16 16 m m n n o o p p The output I need is: 1 1 a a 5 5 e e 9 9 i i 13... (5 Replies)
Discussion started by: mvaishnav
5 Replies

7. Shell Programming and Scripting

mysql how to select a specific row from a table

i have a table records ------------ id | user | time | event 91 admin | 12:00 | hi 92 admin | 11:00 | hi 93 admin | 12:00 | bye 94 admin | 13:00 | bye 95 root | 12:00 | hi 96 root | 12:30 | hi 97 root | 12:56 | hi how could i only select and display only the user and event from... (6 Replies)
Discussion started by: kpddong
6 Replies

8. UNIX for Dummies Questions & Answers

Extract minimum values among 3 columns

Hi. I would like to ask for helps on extracting a minimum values among three columns using gawk in tab separator. input file: as1 10 20 30 as2 22 21 23 as3 300 391 567 as4 19 20 15 Output file: as1 10 as2 21 as3 300 as4 15 I am extremely appreciate your helps and comments.... (2 Replies)
Discussion started by: Amanda Low
2 Replies

9. Shell Programming and Scripting

Finding Minimum value per Row range of data

Here is an example of a file I am working with: C 4704 CB 1318 ASP 115 BGRF 1 weak 0.0% 4.33 C 4720 OD 1322 ASP 115 BGRF 1 weak 0.0% 3.71 O 4723 OD 1322 ASP 115 BGRF 1 weak 0.0% 3.48 O 4723 CG 1321 ASP 115 BGRF 1 weak 0.0% 4.34... (3 Replies)
Discussion started by: userix
3 Replies
Login or Register to Ask a Question