A string for selecting rows basing on a MAX value

1

Hi,
I would like to create an SQL string to give me records basing on a MAX value. This value is DATE.
The string that I use and is not working is:

SELECT DISTINCT Konta.Symbol, ObrotyKsiegowe.SaldoWn, ObrotyKsiegowe.Data, Konta.Typ
FROM ep15_opisie.dbo.Konta Konta, ep15_opisie.dbo.ObrotyKsiegowe ObrotyKsiegowe
WHERE ObrotyKsiegowe.Konto = Konta.ID AND (ObrotyKsiegowe.Data<={ts ' 2015-05-31 00:00:00'}) AND ObrotyKsiegowe.Data = (SELECT MAX(ObrotyKsiegowe.Data)
FROM ep15_opisie.dbo.Konta Konta, ep15_opisie.dbo.ObrotyKsiegowe ObrotyKsiegowe
WHERE ObrotyKsiegowe.Konto = Konta.ID AND (ObrotyKsiegowe.Data<={ts ' 2015-05-31 00:00:00'}))

I cannot upload images but this string selects only records with ObrotyKsiegowe.Data = 2015-05-31. What I want to do, is to have something like that:

a abcde 2015-03-31
b abcde 2015-02-15
c abcde 2015-03-31

and my query gives me:

a abdde 2015-03-31
c abcde 2015-03-31

And it ignores the 'b' record completely. Oh, and one more thing. The database would look like this:
a abcde 2015-01-01
a abcde 2015-01-02
a abcde 2015-01-03 and so on.

Can anyone help? Thanks!

2

I think you mean SQL query not SQL string. Those are two very different things.

What is the datatype of ObrotyKsiegowe.Data?

Also, are you sure that there exists a row with ID = 'b' in both tables?

Also, rewrite your query to use standard JOIN clauses:

SELECT DISTINCT Konta.Symbol
	,ObrotyKsiegowe.SaldoWn
	,ObrotyKsiegowe.Data
	,Konta.Typ
FROM ep15_opisie.dbo.Konta Konta
INNER JOIN ep15_opisie.dbo.ObrotyKsiegowe ObrotyKsiegowe
	ON ObrotyKsiegowe.Konto = Konta.ID
WHERE (ObrotyKsiegowe.Data <= {ts ' 2015-05-31 00:00:00' })
	AND ObrotyKsiegowe.Data = (
		SELECT MAX(ObrotyKsiegowe.Data)
		FROM ep15_opisie.dbo.Konta Konta
			,ep15_opisie.dbo.ObrotyKsiegowe ObrotyKsiegowe
		WHERE ObrotyKsiegowe.Konto = Konta.ID
			AND (ObrotyKsiegowe.Data <= {ts ' 2015-05-31 00:00:00' })
		)

When you do so, you can easily see that the first WHERE condition is redundant, given the second one (the one using the subquery),

1 Like
3

Sorry for my mistake, string = query and now everything is fine :smile:

Yes, there is ID = 'b' in both tables and the datatype of ObrotyKsiegowe.Data is date - a standard numeric also format used in MS Excel.

Thanks for your help, I'll have to think about it when I have some free time.

I'm just beginning my journey with SQL. Would you recommend any good books to read?

4

Fantastic stuff here:

T-SQL Querying

Also, if the SQL datatype of ObrotyKsiegowe.Data is date, then the condition can be expressed more simply:

WHERE ObrotyKsiegowe.Data <= '20150531'
5

WHERE ObrotyKsiegowe.Data <= '20150531'

Yes, but "2015-05-31 00:00:00" is derivred from user-input from MS Excel and it is a variable in a macro. So this is kind of easier for me.

Thanks for the book, I will certainly look it up.