4

When using SELECT * FROM myTable I get a list that looks like this:

A    473
A    234
B    752
C    136

However, I don't want to get several rows with the same letter. I just want to get the first entry with a certain letter and then skip to the next, like so:

A    473
B    752
C    136

Now, I know how to do this when I know what type of data I have. However, I want to write a SQL command that works regardless of what the table looks like, that is, something along the lines: SELECT * FROM myTable WHERE duplicates of column1 are discarded.

EDIT: I doesn't really need to be the first entry that gets selected for each unique column1 value. Any row will do.

EDIT 2: I very much would like for the sollution to work both on strings and numbers.

Community
  • 1
  • 1
Speldosa
  • 1,900
  • 5
  • 21
  • 36
  • 1
    What database are you using? How do you identify what the first value is? SQL tables have no inherent ordering. – Gordon Linoff Jul 26 '12 at 21:49
  • I'm using an access database, and I'm accessing it through odbc in PHP. Regarding the ordering, I really don't care which row gets selected. – Speldosa Jul 26 '12 at 21:56

3 Answers3

3

You could use some sort of aggregate function to get the value for column2 that you want. Example:

SELECT Column1, MAX(Column2)
FROM MyTable
GROUP BY Column1

You can read up on the various aggregate functions and what they do here. That link is specific to SQL Server, but the same idea applies to many DBMS' out there.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
1

You can use GROUP BY. SELECT column1 FROM myTable GROUP BY column1 would work:

A
B
C

If you want column2, you'll need to pick an aggregate function, such as MAX. SELECT column1,MAX(column2) FROM myTable GROUP BY column1:

A    473
B    752
C    136

That will give each row in myTable with a given column1 and the highest column2.

craig65535
  • 3,439
  • 1
  • 23
  • 49
  • The question specifically asked for the first value in the sequence. – Gordon Linoff Jul 26 '12 at 21:50
  • 1
    Like your comment above said, we don't know what "first value" means without knowing more about the table. Is there an ID column? From the example I assumed MAX would be a good starting point to explain the use of GROUP BY. – craig65535 Jul 26 '12 at 21:53
  • If column2 are string values, can I still use MAX() or would I have to use something else? – Speldosa Jul 26 '12 at 22:00
  • Well, when trying this on when column2 contains string, I get zero rows so I guess MAX() isn't suitable for string values. – Speldosa Jul 26 '12 at 22:13
  • Does your table have any other columns, like an ID column? – craig65535 Jul 26 '12 at 22:40
  • 1
    @Speldosa If you are getting zero rows, there is some other problem. Max works with strings. You should post some sample data – Fionnuala Jul 26 '12 at 22:54
  • Well, what really happens is that I get `-1` as output when I run `echo odbc_num_rows($myNewTable);` in PHP. I don't know how better to inspect it. – Speldosa Jul 26 '12 at 23:27
  • Check something other than the number of rows, the contents of column1, for example. "Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers." -- http://php.net/manual/en/function.odbc-num-rows.php – Fionnuala Jul 27 '12 at 08:03
-1

you can use the function "rownum"

eg: 

select * from table_name where rownum < 2;

o/p: it wil show the first row entry.

Malachi
  • 3,205
  • 4
  • 29
  • 46
saubhagya gupta
  • 1