How to select distinct rows from a table?

Question

I have a table named 'versions' which has columns control_no, revision_id ,revision_dt, version_no, version_dt, created_by, change_type, summary, new_owner.

Each control_no has revision_id ranging from 1 to a specific number. From this table, I want to select VERSION_NO,CHANGE_TYPE,SUMMARY,CREATED_BY for a CONTROL_NO with max(REVISION_ID).

How to do this?

Just listing some column names is not really enough for us to understand your problem. Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Sep 17 '18 at 07:42
please clear your question if you wants to use the max revision_id for each control_no??? — Nikhil S, Sep 17 '18 at 08:51
Hi. This is a(n obvious) faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. When you do need to ask a code question please read & act on [mcve]. — philipxy, Sep 17 '18 at 09:09

score 0 · Answer 1 · answered Sep 17 '18 at 07:56

0

Use the below query

select VERSION_NO,CHANGE_TYPE,SUMMARY,CREATED_BY from VERSIONS where REVISION_ID in (SELECT A.REVISION_LIST FROM (select max(REVISION_ID) AS REVISION_LIST, CONTROL_NO AS CONT_NO from VERSIONS GROUP BY CONTROL_NO)A);

answered Sep 17 '18 at 07:56

Naresh

9
3

score 0 · Answer 2 · edited Sep 17 '18 at 09:46

0

By Below Code you can get

SELECT MAX(Version_id) AS Version_id, VERSION_NO,CHANGE_TYPE,SUMMARY,CREATED_By, CONTROL_NO 
from table
GROUP BY VERSION_NO,CHANGE_TYPE,SUMMARY,CREATED_By, CONTROL_NO

edited Sep 17 '18 at 09:46

answered Sep 17 '18 at 07:56

user2042214

165
9

Nikhil S · Answer 3 · 2018-09-17T09:40:18.627

0

this will work:

 select VERSION_NO,CHANGE_TYPE,SUMMARY,CREATED_BY,max(REVISION_ID),control_no 
  from versions group by control_no;

edited Sep 17 '18 at 09:40

answered Sep 17 '18 at 08:10

Nikhil S

3,786
4
18
32

I think @aiswarya wants to use the max `revision_id` for each `control_no`. – William Robertson Sep 17 '18 at 08:44
@WilliamRobertson now its ok?? – Nikhil S Sep 17 '18 at 09:44

How to select distinct rows from a table?

3 Answers3