Hi, i've tried several times, but haven't succed in making this:
Classroom2
Name: | Date:
Oliver | 12-10-10
Homer | 12-10-10
Peter | 11-11-10
Classroom1
Name: | Date:
James | 20-10-10
Lars | 20-10-10
John | 11-11-10
How do i make a SQL statement that ONLY selects the lastest dates for each classroom like the example above? (the text in red is that i dont want to select)
I've tried with MAX(Column) function and Group By but none of them seems to work on my web application..
By the look of the data your date field does not have the variable type set as a "date". MySQL normally displays date in the format:
DATE 'YYYY-MM-DD'
see the following for more info:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html
the Date this is just an example. Still wondering how to do it.
Classroom2
Name: | Date:
Oliver | 2012-10-10
Homer | 2012-10-10
Peter | 2011-11-10
Classroom1
Name: | Date:
James | 2012-10-10
Lars | 2012-10-10
John | 2011-10-10
This my table:
Classroom : Varchar
Date : datetime
Name : varchar
I'm looking for the select statement, not how to format the date...
the text in red i not to be selected
Classroom2
Name: | Date:
Oliver | 2012-10-10
Homer | 2012-10-10
Peter | 2011-11-10Classroom1
Name: | Date:
James | 2012-10-10
Lars | 2012-10-10
John | 2011-10-10This my table:
Classroom : Varchar
Date : datetime
Name : varcharI'm looking for the select statement, not how to format the date...
the text in red i not to be selected
You need a sub query to find the max first then get the data based on the result. If should be something like this:
Select *
from mytable
where mytable.classroom = 'Classroom1'
and mytable.date = (select max(date)
from mytable where classroom = 'Classroom1')Watch out for the datetime vs. date variable type if you are looking for all things on the same day.
You need a sub query to find the max first then get the data based on the result. If should be something like this:
Select * from mytable where mytable.classroom = 'Classroom1' and mytable.date = (select max(date) from mytable where classroom = 'Classroom1')Watch out for the datetime vs. date variable type if you are looking for all things on the same day.
thank, now i understand how to use the MAX() properly, but i still have a problem. The SQL statement you gave me will only selects the latest date for one of the "tables". What if i wanted to pick up the latest date for each of the "tables"
Ex:
Classroom2
Name: | Date:
Oliver | 10-10-10
Homer | 08-10-10
Classroom1
Name: | Date:
James | 20-10-10
Lars | 01-10-10
I want both of the greens to be displayed. MAX() will only get the 20-10-10 right? and i will not be able to select the other then...
thank, now i understand how to use the MAX() properly, but i still have a problem. The SQL statement you gave me will only selects the latest date for one of the "tables". What if i wanted to pick up the latest date for each of the "tables"
Ex:
Classroom2Name: | Date:
Oliver | 10-10-10
Homer | 08-10-10Classroom1
Name: | Date:
James | 20-10-10
Lars | 01-10-10I want both of the greens to be displayed. MAX() will only get the 20-10-10 right? and i will not be able to select the other then...
Picky Picky Picky..
Try this:
(Select *
from mytable
where mytable.classroom = 'Classroom1'
and mytable.date = (select max(date)
from mytable where classroom = 'Classroom1')
) UNION (
Select *
from mytable
where mytable.classroom = 'Classroom2'
and mytable.date = (select max(date)
from mytable where classroom = 'Classroom2')
)Picky Picky Picky..
Try this:
(Select * from mytable where mytable.classroom = 'Classroom1' and mytable.date = (select max(date) from mytable where classroom = 'Classroom1') ) UNION ( Select * from mytable where mytable.classroom = 'Classroom2' and mytable.date = (select max(date) from mytable where classroom = 'Classroom2') )
Really sorry i have to be like this, but this is not the way if i have infinitive classrooms...
i was thinking to make something like this:
Select *
From mytable
Where myTable.Date =
(Select MAX(Date)
From mytable
Group By Name)but it gives me an error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
Getting trick now...
Ok here goes...
select mytable.name as Name, crdt.crm as Classroom, crdt.maxdate as `Date`from (select distinct mytable.classroom as crm, max(mytable.date) as maxdate from mytable group by mytable.classroom) as crdt left join mytable where mytable.classroom = crdt.crm and mytable.date = crdt.maxdate order by mytable.classroomI use a similar query on a table of mine and it worked...
select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT
Inventory.Category,
Max(Inventory.Price) as maxpr
FROM
Inventory
GROUP BY
Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr
order by data1.CategoryGetting trick now...
Ok here goes...
select mytable.name as Name, crdt.crm as Classroom, crdt.maxdate as `Date`from (select distinct mytable.classroom as crm, max(mytable.date) as maxdate from mytable group by mytable.classroom) as crdt left join mytable where mytable.classroom = crdt.crm and mytable.date = crdt.maxdate order by mytable.classroomI use a similar query on a table of mine and it worked...
select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT Inventory.Category, Max(Inventory.Price) as maxpr FROM Inventory GROUP BY Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr order by data1.Category
Nice, but it gives me a syntax error when i add the thing in red:
select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT
Inventory.Category,
Max(Inventory.Price) as maxpr
FROM
Inventory
GROUP BY
Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr
order by data1.CategoryIf i dont write (select bla bla bla) before the from, it's ok
This is my code. It's a bit messy since i have three tables, but i really dont know how to fix it...
'Classroom' is equal 'a.Name' and c.Date is equal the Date
select a.Name, a.ID, a.Location, a.Fk_Type,
b.Fk_Equipments, b.Type,
c.Fk_MeetRoom, c.Status, c.Note, c.Fk_User, c.Reg_Date
from (Select Distinct a.Name as Name2, MAX(c.Date) as MaxDate
FROM
(MeetRoom as a
Full join MeetRoomTypes as b on a.Fk_Type=b.Type
Full join EquipmentStatus as c on b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom
)
GROUP By a.Name)
WHERE a.Location=@LocationI get syntax in the red text.
I also found this website: http://stackoverflow.com/questions/612231/sql-select-rows-with-maxcolumn-value-distinct-by-another-column
which does the same i think, but that didn't work for me neither
I got it to work with this code:
select roomNdato.mrn as Name, a.ID, a.Location, a.Fk_Type,
b.Fk_Equipments, b.Type,
c.Fk_MeetRoom, c.Status, c.Note, c.Fk_User, c.Reg_Date, roomNdato.MaxDate AS Date
FROM (MeetRoom as a
Full join MeetRoomTypes as b on a.Fk_Type=b.Type
Full join EquipmentStatus as c on b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom)
Left join (Select Distinct a.Name AS mrn, MAX(c.Date) as MaxDate From
(MeetRoom as a
Full join MeetRoomTypes as b on a.Fk_Type=b.Type
Full join EquipmentStatus as c on b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom
) group by a.Name) as roomNdato
on
a.Name = roomNdato.mrn And
c.Date = roomNdato.MaxDate
Where Location=@Location
order by Name, Date DescNow i got a new error, but it's not the sql statement:
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
This must be another subject. Thanks for your help
Nice, but it gives me a syntax error when i add the thing in red:
select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT Inventory.Category, Max(Inventory.Price) as maxpr FROM Inventory GROUP BY Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr order by data1.CategoryIf i dont write (select bla bla bla) before the from, it's ok
Hello,
Sorry about that I may not have stated my last response correctly. In the last message the first block of code was for you the second block was the code I used with my tables to test the syntax. You should have used this:
select
mytable.name as Name,
crdt.crm as Classroom,
crdt.maxdate as `Date`
from (select distinct mytable.classroom as crm,
max(mytable.date) as maxdate
from mytable
group by mytable.classroom) as crdt
left join mytable
where mytable.classroom = crdt.crm
and mytable.date = crdt.maxdate
order by mytable.classroomIt should be reasonably straight forward...
Think the key is getting the max(date) by classroom.
Have a look at the following code (changing "my_table" for your actual table):
Select Classroom, Date, Name
from my_Table t1
where date = (select max(date) from my_table t2 where t1.classroom = t2.classroom)Sorry about that - obviously didnt read the entire thread... Back in my box :)
Hi i modified the sql with a where statement in the end, and now it's fully functionable. For people who might want this in the future, here is the sql:
SELECT roomNdato.mrn AS Name, a.ID, a.Location, a.Fk_Type,b.Fk_Equipments, b.Type,c.Fk_MeetRoom, c.STATUS, c.Note, c.Fk_User, c.Reg_Date, roomNdato.MaxDate AS Date
FROM
(MeetRoom AS a
Full JOIN MeetRoomTypes AS b ON a.Fk_Type=b.Type
Full JOIN EquipmentStatus AS c ON b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom)
LEFT JOIN (SELECT DISTINCT a.Name AS mrn, MAX(c.Date) AS MaxDate
FROM (MeetRoom AS a
Full JOIN MeetRoomTypes AS b ON a.Fk_Type=b.Type
Full JOIN EquipmentStatus AS c ON b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom)
GROUP BY a.Name) AS roomNdato
ON a.Name = roomNdato.mrn
AND c.Date = roomNdato.MaxDate
WHERE Location=@Location
AND a.name = crdt.crm
AND c.date = crdt.maxdate
ORDER BY Name, Date DESCWe're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.