473,728 Members | 1,598 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECT DISTINCT Problem

Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e), CONVERT(varchar (10),Transactio nDate,104)
AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin
Jul 20 '05 #1
5 11688
Dear Group

Here's some more info and why the problem comes up as I just found out.

SELECT DISTINCT TransactionDate FROM i2b_keytransact ionlog

Produces this Result:

2004-04-02 00:12:00
2004-04-02 02:30:00
2004-04-07 02:54:00
2004-04-09 03:13:00

Of course I would get all values as the time is different. In VB all times
show as 00:00:00 so that's why I was confused.
Any chance to do a Distinct on the date only, not the time?

Thanks for your time and efforts!

Martin

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@titan.btinte rnet.com...
Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e), CONVERT(varchar (10),Transactio nDate,104) AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

Jul 20 '05 #2
Ok, if I just use
SELECT DISTINCT CONVERT(varchar (10),Transactio nDate,104) FROM
i2b_keytransact ionlog
then I get the desired result but my problem is that I need to include
Transactiondate as it is stored in the database.

Like this:

02.04.2004 2004-04-02 00:12:00
02.04.2004 2004-04-02 02:30:00
07.04.2004 2004-04-07 02:54:00
09.04.2004 2004-04-09 03:13:00

First column is the text of the drop-down item and the second column is the
value of the drop down item.
Any chance I can do a SELECT DISTINCT on this?

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@sparta.btint ernet.com...
Dear Group

Here's some more info and why the problem comes up as I just found out.

SELECT DISTINCT TransactionDate FROM i2b_keytransact ionlog

Produces this Result:

2004-04-02 00:12:00
2004-04-02 02:30:00
2004-04-07 02:54:00
2004-04-09 03:13:00

Of course I would get all values as the time is different. In VB all times
show as 00:00:00 so that's why I was confused.
Any chance to do a Distinct on the date only, not the time?

Thanks for your time and efforts!

Martin

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@titan.btinte rnet.com...
Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and once as varchar as I'm populating a drop-down with Text/Value pairs. So I can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e),

CONVERT(varchar (10),Transactio nDate,104)
AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin


Jul 20 '05 #3
Ok, if I just use
SELECT DISTINCT CONVERT(varchar (10),Transactio nDate,104) FROM
i2b_keytransact ionlog
then I get the desired result but my problem is that I need to include
Transactiondate as it is stored in the database.

Like this:

02.04.2004 2004-04-02 00:12:00
02.04.2004 2004-04-02 02:30:00
07.04.2004 2004-04-07 02:54:00
09.04.2004 2004-04-09 03:13:00

First column is the text of the drop-down item and the second column is the
value of the drop down item.
Any chance I can do a SELECT DISTINCT on this?
"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:c5******** **@titan.btinte rnet.com...
Dear Group

I'm having trouble with the clause below. I would like to select only
records with a distinct TransactionDate but somehow it still lists
duplicates. I need to select the TransactionDate once as smalldatetime and
once as varchar as I'm populating a drop-down with Text/Value pairs. So I
can't just use 'SELECT DISTINCT TransactionDate FROM...'

I'm grateful for any hints.

SELECT DISTINCT (TransactionDat e), CONVERT(varchar (10),Transactio nDate,104) AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

Thanks for your time & efforts!

Martin

Jul 20 '05 #4
try this:

SELECT DISTINCT convert(datetim e,cast(Transact ionDate as int)),
CONVERT(varchar (10),Transactio nDate,104)
AS LabelTransactio nDate FROM i2b_keytransact ionlog WHERE ProgClientID =
@ProgClientID ORDER BY TransactionDate ASC

bye,
Helmut
Jul 20 '05 #5
Thanks very much Helmut! It works perfectly!

"hwoess" <in****@iis-edv.at> wrote in message news:<40******* *************** *@newsreader01. highway.telekom .at>...
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3666
by: Colman | last post by:
Howdy all! I guess I'm a newbie, because I am stumped (or maybe just too durned tired). Here's what I got... CREATE TABLE `nodecat_map` ( `nodecat_id` mediumint(8) unsigned NOT NULL auto_increment, `nodecat_cat_id` mediumint(8) unsigned NOT NULL default '0', `nodecat_node_id` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`nodecat_id`),
3
6468
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I COULD be wrong... :) I've tried the access group...twice...and all I get is "Access doesn't like ".", which I know, or that my query names are too long, as there's a limit to the length of the SQL statement(s). But this works when I don't try to...
9
10899
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my table:- ID Ref Name DATE 1 AAA Joe 1/2 2 BBB Ken 1/2 3 AAA Len 6/3
6
5551
by: John M | last post by:
Hi, The line below is used to feed a combobox. (It is from a database which is used to log pupil behaviour!) The 'incidents' table contains a list of students who have been involved in incidents. Some may appear several times, hence the Distinct. However, thelist generated should still be sorted by Surname. When I add Order by I'm told it conflict with the 'Distinct'. Surely it does not? SELECT DISTINCT ., . & " " & . FROM...
18
3055
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are only returning one record. She states that with disctinct the query stops as soon as it finds a matching record. Both of us are relative novices in database theory (obviously). Can someone help settle this?
2
1468
by: ray well | last post by:
i have to extract info from a legacy access database, which i can't alter, or run APPEND or UPDATE quries against. i can only use SELECT statments to extract what i need. the database has multiple entries for the same first and last name, i need to generate a dataset that has no duplicate first and last names. the sql statment itself is quite easy SELECT DISTINCT tblNameAndAddress.LastName, tblNameAndAddress.FirstName
6
13569
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2 Message: An expression in the ORDER BY clause in the following position, or starting with "CASE..." in the "ORDER BY" clause is not valid. Reason code = "2".  More exceptions ... DB2 SQL error:...
5
6895
by: Daniel Wetzler | last post by:
Dear MSSQL experts, I use MSSQL 2000 and encountered a strange problem wqhile I tried to use a select into statement . If I perform the command command below I get only one dataset which has the described properties. If I use the same statement in a select into statement (see the second select) I get several datasets with the described properties like I didn't use distinct
2
3076
bergy
by: bergy | last post by:
Hello MS SQL experts, I'm trying to reorganize some data for a friend and I'm running into this problem. Currently he has some duplicate rows that I need to get rid of - only one of the columns has duplicates and then there is a unique primary key. The table looks like this: id color --- ------ 3 red 4 red 5 blue 6 green
0
8899
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8757
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9418
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9129
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8124
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6712
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4524
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3235
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2160
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.