-1

How to get the distinct id from a group of id based on the order of increasing number of times it present.

For Example , input: 3,1,1,2,2,2

Here id 2 present 3 times , id 1 present 2 times and id 3 present 1 time..

here is my output 2,1,3

How to get these with a single query using mysql

VASIS
  • 33
  • 8
  • You are expected to try to **write the code yourself**. After [doing more research](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask a good question](http://stackoverflow.com/help/how-to-ask). Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). – Sloan Thrasher Jun 01 '18 at 12:31
  • How did you get this? `3,1,1,2,2,2` is it user input or is it retrieved from a list of rows? – Praveen Kumar Purushothaman Jun 01 '18 at 12:33

4 Answers4

1

At first, we need to analyse how you have got this input:

3,1,1,2,2,2

The CSV input can be pre-filtered, if it is through:

  • User Input
  • Query Output

If it was a User Input, then there's no way MySQL can directly access the value, unless it is stored as data. In that case, you will be having some kind of PHP or other programming language that sends the data to MySQL. So, assuming it for PHP, what I would do is:

<?php
  $csv = "3,1,1,2,2,2";
  $arr = explode(",", $csv);
  $arr = array_unique($arr);
?>

Now you will have unique values.

If it was a query output, you just need to use DISTINCT keyword.

SELECT DISTINCT(`id`) FROM `table` WHERE `SomeCondition`='Value';

You can also try by using GROUP BY, but using DISTINCT is much faster IMHO. (What's faster, SELECT DISTINCT or GROUP BY in MySQL?)

Praveen Kumar Purushothaman
  • 164,888
  • 24
  • 203
  • 252
1
select distinct id, COUNT(id) from your_table 
group by id 
order by COUNT(id)

heres a simple query with the count as well if you want to check its in the correct order.

t..
  • 1,101
  • 1
  • 9
  • 22
1

Suppose we have 2 tables with us:
1) student: Fields are as follows:
a) id: INTEGER AUTO INCREMENT PRIMARY KEY
b) name: VARCHAR
Sample Data: 

student  
id | name
----------  
1  | A  
2  | B  
3  | C

2) marks: Fields are as follows:
a) id: INTEGER AUTO INCREMENT PRIMARY KEY
b) sid: INTEGER FOREIGN KEY (refers to id field from student table)
c) subject: VARCHAR
d) marks: INTEGER Sample Data:

marks:
id | sid | subject | marks  
--------------------------
1  | 1   | s1      | 40

2  | 2   | s2      | 50

3  | 2   | s1      | 60

4  | 2   | s2      | 70

5  | 3   | s1      | 80

Use below query to get distinct student id's with referring records in descending order: 

SELECT `student`.`id`, COUNT(*) AS `total` FROM `student` INNER JOIN `marks` ON (`student`.`id` = `marks`.`sid`) WHERE 1 GROUP BY `student`.`id` ORDER BY `total` DESC
Prasad Wargad
  • 737
  • 2
  • 7
  • 11
-1

You can use group by to get unique ids.

SQL Query:

select id from table group by id;
Vishal Maru
  • 499
  • 2
  • 8