Message
From
01/02/2023 14:49:30
To
01/02/2023 14:47:52
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Miscellaneous
Thread ID:
01686062
Message ID:
01686065
Views:
37
>>Hi,
>>
>>I am creating a SQL Select that will select ONE record based on two fields. The two fields are DATE_FLD and ORDER_NO field. There could be more than one records with the same DATE_FLD value (e.g. "01/01/2023) but the ORDER_NO will be different for each record. I need to select a record with the largest value in the ORDER_NO field.
>>
>>Here is my test:
>>
>>Will the above give me what I am looking for?
>>
>
>That's overkill. Use:
>
>
>Tamar
Thank you!
Follow up question: If I add a JOIN clause to the above and the JOIN does not find a record, will the resulting Query have 0 records?
>>
>>I am creating a SQL Select that will select ONE record based on two fields. The two fields are DATE_FLD and ORDER_NO field. There could be more than one records with the same DATE_FLD value (e.g. "01/01/2023) but the ORDER_NO will be different for each record. I need to select a record with the largest value in the ORDER_NO field.
>>
>>Here is my test:
>>
>>select TOP 1 CONVERT(VARCHAR(10),DATE_FLD,101) + STR(ORDER_NO), FIELD2, FIELD3 from MyTable order by DATE_FLD, ORDER_NO DESC >>>>
>>Will the above give me what I am looking for?
>>
>
>That's overkill. Use:
>
>
>SELECT DATE_FLD, MAX(ORDER_NO) FROM MyTable GROUP BY DATE_FLD >>
>Tamar
Thank you!
Follow up question: If I add a JOIN clause to the above and the JOIN does not find a record, will the resulting Query have 0 records?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham