Query Question

my data looks like the following
111 0 01/01/2007
111 0 02/01/2007
222 0 04/01/2007
222 0 05/01/2007
555 1 06/01/2007
666 0 06/01/2007
how can i retrieve the following?
111 0 01/01/2007
222 0 04/01/2007
555 1 06/01/2007
666 0 06/01/2007
my goal is to retrive distinct cols 1 and 2, and then the earliest date in
col 3 for distinct records in cols 1 and 2?
any suggestions? I tried different ways of using FIRST, but was
unsuccessful.
Thanks in advance.try something like:
select ID, Nb, Date
from table T
where Date = (Select min(Date) from Table t2
where t.id = t2.id)
"Jeff" <findjeffajob@.emailias.com> wrote in message
news:0468D8F6-CABC-4758-A5E3-864723DDD630@.microsoft.com...
> my data looks like the following
> 111 0 01/01/2007
> 111 0 02/01/2007
> 222 0 04/01/2007
> 222 0 05/01/2007
> 555 1 06/01/2007
> 666 0 06/01/2007
> how can i retrieve the following?
> 111 0 01/01/2007
> 222 0 04/01/2007
> 555 1 06/01/2007
> 666 0 06/01/2007
> my goal is to retrive distinct cols 1 and 2, and then the earliest date in
> col 3 for distinct records in cols 1 and 2?
> any suggestions? I tried different ways of using FIRST, but was
> unsuccessful.
> Thanks in advance.|||"Jeff" <findjeffajob@.emailias.com> wrote in message
news:0468D8F6-CABC-4758-A5E3-864723DDD630@.microsoft.com...
> my data looks like the following
> 111 0 01/01/2007
> 111 0 02/01/2007
> 222 0 04/01/2007
> 222 0 05/01/2007
> 555 1 06/01/2007
> 666 0 06/01/2007
> how can i retrieve the following?
> 111 0 01/01/2007
> 222 0 04/01/2007
> 555 1 06/01/2007
> 666 0 06/01/2007
> my goal is to retrive distinct cols 1 and 2, and then the earliest date in
> col 3 for distinct records in cols 1 and 2?
> any suggestions? I tried different ways of using FIRST, but was
> unsuccessful.
> Thanks in advance.
This works for me:
CREATE TABLE tbl (col1 INT, col2 INT, col3 DATETIME);
INSERT INTO tbl VALUES (111, 0, '20070101');
INSERT INTO tbl VALUES (111, 0, '20070201');
INSERT INTO tbl VALUES (222, 0, '20070401');
INSERT INTO tbl VALUES (222, 0, '20070501');
INSERT INTO tbl VALUES (555, 1, '20070601');
INSERT INTO tbl VALUES (666, 0, '20070601');
SELECT col1, col2, MIN(col3) col3
FROM tbl
GROUP BY col1, col2
ORDER BY col1, col2 ;
col1 col2 col3
-- -- --
111 0 2007-01-01 00:00:00.000
222 0 2007-04-01 00:00:00.000
555 1 2007-06-01 00:00:00.000
666 0 2007-06-01 00:00:00.000
(4 row(s) affected)
David Portassql

Query Problem

I have a table with columns -- ims_num, last_name, pres_num, territory_num

I need retrieve the max pres_num grouped by territory

I got so far

select max(pres_num) from table
group by territory_num

I need to show all the data -- ims_num, last_name, pres_num, territory_numselect *
from tableA ta
where pres_num = (select max(presnum) from tableA where territory_num = ta.territory_num)|||I tired this, but it didn't work.

The problem is the the max(pres_num) of one territory can be the pres_num in another terriotory, and the pres_num might not be the max(pres_num) in that territory.

I hope that made sense.
Thanks so much for helping!|||"I hope that made sense."

nope, it didn't

it made things a lot worse

perhaps you could show some sample rows, and then an example of what the result set should be|||Consider the following example.

SQL> select * from a;

NAME DEPT AMT
---- ---- ----
EMPA DEPTA 80000
EMPB DEPTA 60000
EMPC DEPTB 80000
EMPD DEPTB 60000

SQL> select dept, max(amt)
2 from a
3 group by dept;

DEPT MAX(AMT)
---- ----
DEPTA 80000
DEPTB 80000

SQL> select *
2 from a a1
3 where a1.amt = (select max(amt) from a where dept = a1.dept);

NAME DEPT AMT
---- ---- ----
EMPA DEPTA 80000
EMPC DEPTB 80000

SQL> select a1.name, V.*
2 from a a1
3 INNER JOIN
4 (select dept, max(amt) MAX_AMT
5 from a
6 group by dept) V ON
7 V.dept = a1.dept AND
8 V.MAX_AMT = a1.amt;

NAME DEPT MAX_AMT
---- ---- ----
EMPA DEPTA 80000
EMPC DEPTB 80000

Query Problem

I am trying retrieve all records in a table by the max presciption by group of territory.

In my table I have columns-- IMS_num, last_name, terriorty_num, pres_num

I want to see all the data by territory and max pres_num
I am lost.

I got this far..

select max(pres_num) from table group by territory_num.

I do not know how to get the last_name and territory_num displayed.Perhaps it'll help ...

SELECT territory_num, last_name, max(pres_num)
FROM your_table
GROUP BY territory_num, last_name;

Query performance with order by clause?

Hi all,

Just wondering if anyone can tell me if an order by clause on a select
query would have any impact on the time it takes to retrieve results?

Essentially I'm selecting Top 1 out of a table via various criteria
and currently getting it back without an order by clause. The order by
would only include the column that has the clustered primary index on
it.

Can anyone tell me if in theory this will slow the query down?

Many thanks in advance!

Much warmth,

MurrauM Wells (planetquirky@.planetthoughtful.org) writes:
> Just wondering if anyone can tell me if an order by clause on a select
> query would have any impact on the time it takes to retrieve results?
> Essentially I'm selecting Top 1 out of a table via various criteria
> and currently getting it back without an order by clause. The order by
> would only include the column that has the clustered primary index on
> it.
> Can anyone tell me if in theory this will slow the query down?

For most situations this is an uninteresting question. TOP 1 with an
ORDER BY means "give me one row, I don't care which", but it's not good
for a random selection.

So if you need your row to be deterministically selected, then you must
have an ORDER BY clause.

The cost for the ORDER BY clause is likely to be marginal, if the order
by columns agrees with the clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query logs

Does SQL Server create a log of all the queries run against a database? Or,
alternatively, is there a way to retrieve deleted data from a table?
thnx,
Christoph
No it doesn't keep a log of queries (as in selects) but all dml is recorded
in the transaction log. If you have backups you can restore to a previous
point in time but I guess you don't in which case you pretty much need a
third party tool like Lumigent's log explorer which is able to read the
transaction log and reconstruct deleted data/truncated tables
http://www.lumigent.com/products/le_sql_faq.html
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Christoph" <jcboget@.yahoo.com> wrote in message
news:OhkjsXXKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server create a log of all the queries run against a database?
> Or,
> alternatively, is there a way to retrieve deleted data from a table?
> thnx,
> Christoph
>