That's not really a normalized database design...

If you have a lookup table (like tH) you should reference the primary key (ID) of that table rather than the tcoutcd column you...

What is the reason not to use a foreign key?

Seems like the appropriate solution for the requirement.

ChrisM@home (3/7/2010)

---

...

You've posted a third method for resolving this job, late on a saturday night after a few beers because you had a spare 5 minutes. Puts it all...

Please read the short discussion in this thread:

http://www.sqlservercentral.com/Forums/FindPost854349.aspx. It will show you two alternative ways to do it.

(Side note: I found it by using "ROW_NUMBER 2000" as search terms in...

simflex-897410 (3/6/2010)

---

hi Jeff and Lutz

Here is the DDL (the relevant info).

CREATE TABLE [dbo].[myTable] (

[Key] [int] NOT NULL ,

[functionDate] [datetime] NULL ,

[EventTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY] TEXTIMAGE_ON...

I'm not sure if Database Engine Tuning Advisor should be on that list:

I think DTA sometimes (mostly?) is misguiding in terms of indexing. I'd rather study execution plans or use...

@chris-2: You're using a CTE? How comes?

I would have used a CTE, too. But not as a recursive CTE.

Here's how I'd do it (return the identical data like your query...

I'm by far not at the level of Jeff or...

...or bcp

or linked server

or OPENROWSET...

Hard to tell without any more detailed information...

If California didn't already reserved those terms, I would use CUI (coding under influence) and CWI (coding while intoxicated).

If either one would be allowed at the place I work, I...

The easiest way is to use GROUP BY

SELECT MAX(id) AS id,memNo,date_trans

FROM @tbl

GROUP BY memNo,date_trans

ORDER BY MAX(id)

Side note: It is bad practice to store date values as...

One question is still unanswered as far as I'm concerned:

What is the data type of your functionDate column?

Please provide the related section from the DDL script (CREATE TABLE script).

Currently, we...

The reason for me not to provide any code (yet) is the simple lack of sample data.

The brief sample you posted doesn't match your expected result set.

If you could provide...

You might want to use ROW_NUMBER() together with a CrossTab query as described in the related link in my signature.

If you need further assistance please provide table structure and sample...

If you'd run your subquery separately you'd see that it will return only one row.

Assuming RowNumber is a normal column the you simply could use the GROUP BY function.

Something...