Re: Sorting information using query analyser
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/10/04
- Previous message: Chris Scott: "Sorting information using query analyser"
- In reply to: Chris Scott: "Sorting information using query analyser"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 10 Aug 2004 18:24:21 +0200
On Tue, 10 Aug 2004 08:43:02 -0700, Chris Scott wrote:
>Hi all,
>
>I am trying to sort a really large file of information
>and I need some help as I cant work out how to get some
>of it.
>the fields I have are:
>csbytes
>cscookie
>csuriquery
>I need to program to get:
Hi Chris,
Allow me to put you straigh on some terminology first. If you use an RDBMS
like SQL Server, you don't have files, fields, records or programs, but
you have tables, columns, rows and queries, stored procedures and/or
triggers. You might say that thesse are only terms, but they're not. They
relate to completely different concepts. A program tells a computer how to
do something, step by step - a query tells the RDBMS what results you
want; the RDBMS will figure out how to do it. A file is an ORDERED
collection of records - a table is an UNordered colledtion of rows. A
field usually refers to some consecutive bytes in a record that may
contain any kind of data with any meaning - a row comes with a pre-set
domain that all values must adhere to. Etc.
You must change your mindset if you switch to using an RDBMS.
>A list of the distinct values in cs bytes ( got have done
>this one using: SELECT DISTINCT (csbytes) FROM
>*filename*). However the list doesnt work properly as
>when i use ORDER BY I get 0, 10000, 1001, etc so if
>anyone knows how to order by the length of the field and
>then in ascending order that would be REALLY useful.
I guess it would, but wouldn't it even be more useful if you knew how to
order by the numeric equivalent of csbytes?
(a) useful
SELECT DISTINCT csbytes
FROM MyTable
ORDER BY LEN(csbytes), csbytes
(b) more useful
SELECT DISTINCT csbytes
FROM MyTable
ORDER BY CAST (csbytes AS integer)
Note: these queries use proprietary syntax. The official ANSI standard
doesn't allow expressions in the ORDER BY list. SQL Server does allow this
kind of queries, but other products may disallow this.
>A count for the number of times each of these distinct
>values appear in the csbytes list.
SELECT csbytes, COUNT(*)
FROM MyTable
GROUP BY csbytes
>A list of the first 100 longest values in cscookie.
SELECT TOP 100 cscookie
FROM MyTable
ORDER BY LEN(cscookie) DESC
See the above note about the ORDER BY clause. The TOP clause is a SQL
Server specific extension to the SQL language as well.
>A lost of the first 100 longest values in csuriquery.
Same idea as above.
>I also need to be able to compare cscookie and csuriquery
>with a list of characters and find out if any of them
>have characters that are not in the list I have, and to
>display a boolean false if that is the case.
There is no such thing as a boolean datatype in SQL. You're probably
better off returning a text (e.g. "Not in list").
Before I can help you with this query, I need to know the table structure
of both tables involved. Please post DDL (CREATE TABLE statements,
including all constraints - you may omit irrelevant columns). Post sample
data (in the form of INSERT statements that I can cut and paste for
testing) and expected output as well, to decrease the chance of
misinterpretation.
>If anyone can help me on any of these I would really
>appreciate it as I am totally stuck and can't find what I
>need online or in the SQL book I have.
Maybe you should get a better book then - some of the questions you ask
are pretty basic and should be covered in any decent SQL book. By the way,
do you know that SQL Server comes with an extensive online reference? Try
clicking Start / Program Files / Microsoft SQL Server / Books Online.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Previous message: Chris Scott: "Sorting information using query analyser"
- In reply to: Chris Scott: "Sorting information using query analyser"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
