Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.
SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.

Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.sql

Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
--
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.

help with query

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:524963
Hi,
I need to modify the data in a column in CODE.stock_code. Some of the
records are in the form of 250-10-12-9. Not all records are the same lengh,
but I need to remove the -9 in those records.
This newbie would appreciate your help in the sql that would do this.
regards,
g
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04Will the "-9" always appear as the last two characters in the value? If that
is
the case, then you could do the following:
Update Code
Set Stock_Code = Left(Stock_Code, Len(Stock_Code) - 2)
Where Left(Stock_Code, 2) = '-9'
Thomas
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:L38ee.11476$3U.717136@.news20.bellglobal.com...
> Hi,
> I need to modify the data in a column in CODE.stock_code. Some of the
> records are in the form of 250-10-12-9. Not all records are the same lengh
,
> but I need to remove the -9 in those records.
> This newbie would appreciate your help in the sql that would do this.
>
> regards,
> g
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>|||You said "need to remove the -9 in those records" -- which records (rows)
are "those" ? We need to know more details.. Do you need to remove the '-9'
from any row that Has a '-9' at the end? Id so, then just
Update Table Set
ColName = Left(ColName, Len(ColName) - 2)
Where ColName like '%-9'
-- or is there another rule?
"Neither rhyme nor reason" wrote:

> Hi,
> I need to modify the data in a column in CODE.stock_code. Some of the
> records are in the form of 250-10-12-9. Not all records are the same lengh
,
> but I need to remove the -9 in those records.
> This newbie would appreciate your help in the sql that would do this.
>
> regards,
> g
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>
>|||You should never have a table named "Code" -- what kind of code is it'
-9 in those records [sic]. <<
You need to learn to use the right words so you will have the right
mindset. Rows are not records and you update columns. Try this.
UPDATE Inventory
SET stock_code
= REVERSE (SUBSTRING (REVERSE(stock_code), 3))
WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
There are also proprietary LEFT() and RIGHT() substring functions, but
these are reserved words in Standard SQL. However, they will probably
be faster.|||Joe, shouldn't that be
Update Inventory
Set Stock_Code = Reverse(Substring(Reverse(Stock_Code), 3))
Where Substring(Reverse(Stock_Code), 1, 2) = '9-'
With the string reversed, the 9 will appear before the dash.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
> You should never have a table named "Code" -- what kind of code is it'
>
> -9 in those records [sic]. <<
> You need to learn to use the right words so you will have the right
> mindset. Rows are not records and you update columns. Try this.
> UPDATE Inventory
> SET stock_code
> = REVERSE (SUBSTRING (REVERSE(stock_code), 3))
> WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
> There are also proprietary LEFT() and RIGHT() substring functions, but
> these are reserved words in Standard SQL. However, they will probably
> be faster.
>|||Thank you all for your help... So my wording should have been
I need to remove the last 2 digits from rows that contain -9 in the
STOCK_CODE column ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
> You should never have a table named "Code" -- what kind of code is it'
>
> -9 in those records [sic]. <<
> You need to learn to use the right words so you will have the right
> mindset. Rows are not records and you update columns. Try this.
> UPDATE Inventory
> SET stock_code
> = REVERSE (SUBSTRING (REVERSE(stock_code), 3))
> WHERE SUBSTRING (REVERSE(stock_code), 1, 2) = '-9';
> There are also proprietary LEFT() and RIGHT() substring functions, but
> these are reserved words in Standard SQL. However, they will probably
> be faster.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04|||Then my modified version of Joe's code should do it:
Update Inventory
Set Stock_Code = Reverse(Substring(Reverse(Stock_Code), 3))
Where Substring(Reverse(Stock_Code), 1, 2) = '9-'
Thomas
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:ogpee.13540$3U.936989@.news20.bellglobal.com...
> Thank you all for your help... So my wording should have been
> I need to remove the last 2 digits from rows that contain -9 in the
> STOCK_CODE column ?
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115239637.214708.255220@.o13g2000cwo.googlegroups.com...
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>

help with large log files

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.clients:30825
Hi,
In SQL Server Enterprise Manager > Space Allocation. I can not reduce the
current size. I can restrict the to current size but I can not reduce it.
This lay person very much appreciates your help.
regards
S
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
Have alook at these:
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Neither rhyme nor reason" <sportman_6663@.yahoo.ca> wrote in message
news:4Fl0e.8116$JK1.566630@.news20.bellglobal.com.. .
> Hi,
> In SQL Server Enterprise Manager > Space Allocation. I can not reduce the
> current size. I can restrict the to current size but I can not reduce it.
> This lay person very much appreciates your help.
>
> regards
> S
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04
>