A SQL that must be supported by the Access, MySQL, Microsoft SQL servers

A SQL that must be supported by the Access, MySQL, Microsoft SQL servers. The table is like this:

name1 name2 date     cant
a1    b1    2-3-2003  12
a1    b1    1-3-2003  11
a1    b2    3-3-2003  13
a1    b2    2-3-2003  14
a2    b3    2-3-2003  12
a2    b3    1-3-2003  11

I want to obtain the result:

name1 name2 date     cant
a1    b1    2-3-2003  12
a1    b2    3-3-2003  13
a2    b3    2-3-2003  12

leonteanupotelinAsked:
Who is Participating?
 
lucho_ndConnect With a Mentor Commented:
Hi!!

Why don't you make three versions of the query and execute it depending of the current server.

good luck

lucho_nd
0
 
leonteanupotelinAuthor Commented:
answer
0
 
leonteanupotelinAuthor Commented:
answer
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
BWarmuskerkenCommented:
Select name1, name2, date, cant from YourTable where date >= #2/3/2003#
0
 
GlowmanCommented:
SELECT * FROM Table1 Where
cant=12 or cant=13;

or is there some other way you want to come up with your return set?
0
 
lucho_ndCommented:
Hi!:
I assume the table's name is Table1

select name1, name2, max(date), cant from table1
group by name2 order by name2

good luck

lucho_nd
0
 
leonteanupotelinAuthor Commented:
answer
0
 
BWarmuskerkenCommented:
I'm sure we could all come up with a bunch of different ways to return the same sub set.  We need more detail as what you are basing the selection on.   Is it the date? The cant?  Is it where name1 = a1 and the date is greater than 2/1/03? etc...
0
 
leonteanupotelinAuthor Commented:
Just lucho_nd understand my question, but this variant don't work in access. I want a Sql query that must work with any of the above DBMS.
0
 
leonteanupotelinAuthor Commented:
select name1, name2, max(date), cant from table1
group by name2 order by name2

This query work, but just in SQL and MySQL DBMS. I want a query that work with any of DBMS (SQL, MySQL, Access)...
0
 
leonteanupotelinAuthor Commented:
answer
0
 
leonteanupotelinAuthor Commented:
answer
0
 
leonteanupotelinAuthor Commented:
select name1, name2, max(date), cant from table1
group by name2 order by name2

With this SQL Query I obtain the error:

"error '80040e14' [Microsoft][ODBC Microsoft Access Driver] You tried to execute a
query that does not include the specified expression 'articlenumber' as part ... "
0
 
leonteanupotelinAuthor Commented:

error '80040e14' [Microsoft][ODBC Microsoft Access Driver] You tried to execute a
query that does not include the specified expression 'articlenumber' as part of an aggregate function ...
0
 
leonteanupotelinAuthor Commented:
answer
0
 
leonteanupotelinAuthor Commented:
answer
0
 
leonteanupotelinAuthor Commented:
answer lucho_nd
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
Arthur_WoodCommented:
just out of curiosity, where does the field "articlenumber" come from.  I do NOT see that field name in the query:

select name1, name2, max(date), cant from table1
group by name2 order by name2

That you SAID you used, when you got the error.

If that was NOT the REAL SQL that you used, then PLEASE show us the REAL SQL that you DID use.

AW
0
 
leonteanupotelinAuthor Commented:
answer all
0
 
lucho_ndCommented:
Hi!:

The error refer to a field called 'articlenumber', what field is that????

The SQL query I send is correct. You should try it in Query Wizard from Access. And try change the name Date (for ex Date1) also.

Good luck

lucho_nd
0
 
leonteanupotelinAuthor Commented:
Ok, then the error is:
error '80040e14' [Microsoft][ODBC Microsoft Access Driver] You tried to execute a
query that does not include the specified expression 'name1' as part of an aggregate function ...

This was just an exemple like my case and the error was from another place. The error above is the true error...
0
 
leonteanupotelinAuthor Commented:
lucho_nd, try the query from visual basic and you will see the error above...
0
 
leonteanupotelinAuthor Commented:
answer
0
 
lucho_ndCommented:
Try this, but I don´t know if this query run in all the Servers.

select name1, grouptable.name2, maxdate, cant
from table1, (select name2, max(date) as maxdate
              from table1
              group by name2) grouptable
where table1.name2 = grouptable.name2
     and table1.date = grouptable.maxdate
order by grouptable.name2


The other option is use two select sentences

select name2, max(date) as maxdate
from table1
group by name2 into grouptable

select name1, grouptable.name2, maxdate, cant
from table1, grouptable
where table1.name2 = grouptable.name2
     and table1.date = grouptable.maxdate
order by grouptable.name2


good luck

lucho_nd
0
 
leonteanupotelinAuthor Commented:
hello lucho_nd

Your answer is interesting but I want to test it with all DBMS and I will be back...
thank you
0
 
leonteanupotelinAuthor Commented:
I don't create a new table like 'grouptable'.
0
 
leonteanupotelinAuthor Commented:
I don't want to create a new table like 'grouptable'.
0
 
leonteanupotelinAuthor Commented:
I don't want to create a new table like 'grouptable'.
0
 
lucho_ndCommented:
Hi!!

The first option would be the only one. It execute correctly in SQL Server.

good luck
lucho_nd

select name1, grouptable.name2, maxdate, cant
from table1, (select name2, max(date) as maxdate
             from table1
             group by name2) grouptable
where table1.name2 = grouptable.name2
    and table1.date = grouptable.maxdate
order by grouptable.name2


0
 
leonteanupotelinAuthor Commented:
Thank you verry mutch, but this query don't work in MySql server :)...nested queries!!!
0
 
lucho_ndCommented:
Hi!!
there are no other choice, take it or leave it :-D

good luck

lucho_nd

0
 
leonteanupotelinAuthor Commented:
Ok, you are the winner but my problem is until unresolved:(
nashpa
0
 
leonteanupotelinAuthor Commented:
I want to make a portable program. The problem is with the Access because I don't make a query. I resolv the problem but dinamicaly by manipulate the recordset.  
0
 
leonteanupotelinAuthor Commented:
Thank you
0
All Courses

From novice to tech pro — start learning today.