SELECT statement with multiple WHERE criteria (MS-Access)

Question

Below is the sample data:

c1   c2   c3   c4   c5
1    a1   a     1     1
2    a2   a     2     1
3    a3   a     3     1
4    a4   a     4     1
5    b1   b     1     1
6    b2   b     2     1
7    b3   b     3     1
8    b4   b     4     1
9    a1   c     3     1

I want to get the the below details:

c1  c2  c3  c4  c5
1    a1  a   1    1
5    b1  b   1    1
9    a1  c   3    1

C1 is primary key, the criteria is for any given unique(c2) where c4 is the lowest, I want to return the contents(all the 5 columns) of the row.

[What ahve **you** tried?](http://www.whathaveyoutried.com) - show us **some effort** then we'll be glad to chip in and help... — marc_s, Nov 10 '12 at 08:56

Mahmoud Gamal · Accepted Answer · 2012-11-13T05:37:51.087

2

Try this:

SELECT t1.*
FROM Table1 t1
INNER JOIN
(
  SELECT c3, MIN(c4) c4 
  FROM Table1
  GROUP BY c3
) t2 ON t1.c3 = t2.c3 ANd t1.c4 = t2.c4

SQL Fiddle Demo

Update:¹ In SQL the returned results is a set set(unless you specify an ORDER BY clause, it is a cursor in this case), wherein the order is not guaranteed. This is a standard. You should use an ORDER BY clause if you want to guarantee a specific order. In your case , the results is not guaranteed to be ordered like 1 5 9. Add ORDER BY c1 instead.

The ORDER BY clause might be crucial in some cases, for example, if want to get the top three rows, or the maximum one, in this case you have to specify an ORDER BY clause.

So if you wants to persist a specific order the you have specify an ORDER BY.

^{¹ As noted by @Fahim Parker, see the comments below.}

edited Nov 13 '12 at 05:37

answered Nov 10 '12 at 09:02

Mahmoud Gamal

78,257
17
139
164

Ok guys hold on for one sec, let me tell you that I want the code that works in MS-Access, sorry I did not mention it in first place – aaaa bbbb Nov 10 '12 at 09:11
@aaaabbbb - Its my pleasure. Glad to help. – Mahmoud Gamal Nov 10 '12 at 09:18
please read [this](http://stackoverflow.com/a/11130318/1066828). **SELECT is not guaranteed to return rows in any specific order (without using an ORDER BY clause, of course)** – Fahim Parkar Nov 10 '12 at 09:18
@FahimParkar - Yes it is true. In SQL the returned results is a [***set***](http://en.wikipedia.org/wiki/Set_%28mathematics%29), where the order is not guaranteed. This is a standard. You should use an `ORDER BY` clause if you need it to gurantee a specific order. Like in your case with `MAX`. Also in this case, this is true as well, there is no guarantee that the rows returned with a specific order. If the OP wants to persist a specific order he can use an `ORDER BY` clause as well. But what is the problem with my answer with this part. – Mahmoud Gamal Nov 10 '12 at 09:31
as per OP input your answer is right. BUT thought to share my point to OP as input is based on SELECT. – Fahim Parkar Nov 10 '12 at 09:34
@FahimParkar - Ok, Thanks for your point. A lot of people are missing this fact. – Mahmoud Gamal Nov 10 '12 at 09:36
@MahmoudGamal : yeah, for small organization this is not that much. for for big database, this point is very much STRONG. Hence I include AUTO_INCREMENT column in my each table (wherever needed) :) – Fahim Parkar Nov 10 '12 at 09:38

score 0 · Answer 2 · answered Nov 10 '12 at 09:03

0

select c1,c2,c3,c4,c5
from table
where c4= (select min(c4) from table as f where f.c4 = table.c4);

i hope that helps

answered Nov 10 '12 at 09:03

Saddam Abu Ghaida

6,381
2
22
29

instead of `select c1,c2,c3,c4,c5` you could have said `select *` – Fahim Parkar Nov 10 '12 at 09:12
1

Could, but there's no better or worse. – J. Steen Nov 10 '12 at 09:22

SELECT statement with multiple WHERE criteria (MS-Access)

2 Answers2

SQL Fiddle Demo